我有以下資料...
| 用戶界面 | 組名 |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 0 |
| 8 | 0 |
| 9 | 2 |
| 10 | 2 |
| 11 | 2 |
我想對這些組進行唯一排序，以便我得到....
| 用戶界面 | 組名 | 新組 ID |
|---|---|---|
| 1 | 0 | 0 |
| 2 | 0 | 0 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | 1 |
| 6 | 1 | 1 |
| 7 | 0 | 2 |
| 8 | 0 | 2 |
| 9 | 2 | 3 |
| 10 | 2 | 3 |
| 11 | 2 | 3 |
誰能幫我在 SQL (SQL Server) 中做到這一點
uj5u.com熱心網友回復：
with cte as
(
select * from (values
(1 , 0),
(2 , 0),
(3 , 1),
(4 , 1),
(5 , 1),
(6 , 1),
(7 , 0),
(8 , 0),
(9 , 2),
(10 , 2),
(11 , 2))
t([uid], [groupid])
),
cte2 as
(
select
[uid], [groupid],
newGroupId =
row_number() over(order by [uid])
-
row_number() over(partition by [groupid] order by [uid])
from
cte
)
select
[uid], [groupid],
newGroupId = dense_rank() over(order by newGroupId) - 1
from
cte2;
uj5u.com熱心網友回復：
與 Vadium 的方法略有不同，但需要考慮的另一種選擇：
;WITH CTE_MAIN AS
(
select * from (values
(1 , 0),
(2 , 0),
(3 , 1),
(4 , 1),
(5 , 1),
(6 , 1),
(7 , 0),
(8 , 0),
(9 , 2),
(10 , 2),
(11 , 2))
t([uid], [groupid])
),
CTE_2 AS
(
SELECT uid, groupid, LAG(groupid,1,-1) OVER(ORDER BY uid) LastGroupID
FROM CTE_MAIN
)
SELECT uid, groupid, SUM(CASE WHEN GroupID <> LastGroupID THEN 1 ELSE 0 END) OVER(ORDER BY uid) - 1 NewGroup
FROM CTE_2
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361949.html
下一篇：從XML列讀取屬性值