5

## [MS SQL] 連續問題 - 依照購買順序排序店家

### 題目:

• UserNo: 使用者代碼
• StoreNo: 店家代碼
• ProductNo: 商品代碼
• Order: 購買順序
UserNo StoreNo ProductNo Order
001 S01 P01 1
001 S01 P02 2
001 S02 P02 3
001 S02 P03 4
001 S02 P04 5
001 S01 P05 6
001 S01 P06 7
001 S01 P07 8
002 S01 P08 1
002 S02 P09 2
003 S01 P01 1
003 S02 P01 2
003 S02 P02 3

### 期望的結果:

UserNo StoreNo ProductNo Order StoreOrder
001 S01 P01 1 1
001 S01 P02 2 1
001 S02 P02 3 2
001 S02 P03 4 2
001 S02 P04 5 2
001 S01 P05 6 3
001 S01 P06 7 3
001 S01 P07 8 3
002 S01 P08 1 1
002 S02 P09 2 2
003 S01 P01 1 1
003 S02 P01 2 2
003 S02 P02 3 2

### 測試資料:

``````DECLARE @BuyList TABLE
(
UserNo NVARCHAR(10),
StoreNo NVARCHAR(10),
ProductNo NVARCHAR(10),
[Order] INT
)
(UserNo, StoreNo, ProductNo, [Order])
VALUES
('001', 'S01', 'P01', 1),
('001', 'S01', 'P02', 2),
('001', 'S02', 'P02', 3),
('001', 'S02', 'P03', 4),
('001', 'S02', 'P04', 5),
('001', 'S01', 'P05', 6),
('001', 'S01', 'P06', 7),
('001', 'S01', 'P07', 8),
('002', 'S01', 'P08', 1),
('002', 'S02', 'P09', 2),
('003', 'S01', 'P01', 1),
('003', 'S02', 'P01', 2),
('003', 'S02', 'P02', 3)
``````

### 解法:

``````| UserNo | StoreNo | Order | Diff |
|--------|---------|-------|------|
|   001  |   S01   |   1   |   1  |
|   001  |   S01   |   2   |   0  |
|   001  |   S02   |   3   |   1  |
``````

``````| UserNo | StoreNo | Order | Diff | Sum |
|--------|---------|-------|------|-----|
|   001  |   S01   |   1   |   1  |  1  |
|   001  |   S01   |   2   |   0  |  1  |
|   001  |   S02   |   3   |   1  |  2  |
``````

``````SELECT T.*,
(
SELECT SUM(Diff)
FROM(
SELECT *, CASE WHEN (LAG(StoreNo) OVER(ORDER BY [Order]))
=StoreNo THEN 0 ELSE 1 END Diff
WHERE S.UserNo=T.UserNo
) AS S
WHERE S.[Order] <= T.[Order]
) AS StoreOrder
ORDER BY UserNo, [Order]
``````

``````;WITH CTE AS
(
SELECT *, CASE WHEN LAG((UserNo + StoreNo))
OVER (ORDER BY UserNo, [Order])=(UserNo + StoreNo)
THEN 0 ELSE 1 END Diff
)

SELECT UserNo, StoreNo, ProductNo, [Order],
SUM(Diff)
OVER (PARTITION BY UserNo ORDER BY UserNo, [Order]) AS StoreOrder
FROM CTE
ORDER BY UserNo, [Order]
``````

### 參考文章:

[SQL連續範圍] 數字，日期連續範圍

### 1 則留言

1

iT邦新手 2 級 ‧ 2018-06-24 16:13:16

fysh711426 iT邦研究生 5 級‧ 2018-06-26 00:06:56 檢舉