22,209
社区成员
发帖
与我相关
我的任务
分享
WITH /* 测试数据
table1(Col1,Col2) AS (
SELECT 'A',9 UNION ALL
SELECT 'B',9 UNION ALL
SELECT 'C',9 UNION ALL
SELECT 'D',3 UNION ALL
SELECT 'E',3 UNION ALL
SELECT 'F',3
), */
t1 AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY GETDATE()) rn
FROM table1
)
,t2 AS (
SELECT Col2,
ROW_NUMBER() OVER(ORDER BY MIN(rn)) groupID
FROM t1
GROUP BY Col2
)
--SELECT * FROM t2
SELECT t.Col1,
t2.GroupID
FROM t2
JOIN table1 t
ON t2.Col2 = t.Col2
WHERE t2.groupID = 1 -- 也可以用 t.Col1 = 'A' 条件去取 GroupID
Col1 GroupID
---- --------------------
A 1
B 1
C 1
Col2 groupID
----------- --------------------
9 1
3 2