## 測試 Table 及資料

CREATE TABLE [dbo].[Prize](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Status] [int] NOT NULL,
 CONSTRAINT [PK_Prize] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[Prize] ([Id], [Name], [Quantity], [Status]) VALUES (1, N'Product1', 10, 1)
GO
INSERT [dbo].[Prize] ([Id], [Name], [Quantity], [Status]) VALUES (2, N'Product2', 10, 1)
GO
INSERT [dbo].[Prize] ([Id], [Name], [Quantity], [Status]) VALUES (3, N'Product1', 11, 1)
GO
INSERT [dbo].[Prize] ([Id], [Name], [Quantity], [Status]) VALUES (4, N'Product2', 11, 1)
GO
INSERT [dbo].[Prize] ([Id], [Name], [Quantity], [Status]) VALUES (5, N'Product1', 12, 1)

- 利用 PARTITION BY 產生 SortId
- 可以得到下圖的樣子

Select *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY [Quantity] DESC) AS SortId
From [Campaign].[dbo].[Prize]
ORDER BY Name


- 利用 Temp Table 暫存 (才不用產生兩次 SortId 資料)
- 再利用 SortId 做 Join
- 參考以下方式

--新增 Temp Table
CREATE TABLE #TempPrize
(
    Id int,
    [NAME] nvarchar(50),
	Quantity int,
	Status int,
	SortId int
)
-- 將資料新增到 Temp Table
INSERT INTO #TempPrize
Select *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY [Quantity] DESC) AS SortId
From [Campaign].[dbo].[Prize]
ORDER BY Name

-- 查詢結果
SELECT T1.Quantity - ISNULL(T2.Quantity,0)
FROM #TempPrize T1
LEFT JOIN #TempPrize T2 ON T1.NAME = T2.NAME AND T1.SortId = T2.SortId -1

- 查詢結果