CREATE TABLE #t(
p varchar(5), --名字
gp VARCHAR(5) --組號
)
--第一個關系組
INSERT INTO #t VALUES('a','1')
INSERT INTO #t VALUES('b','1')
INSERT INTO #t VALUES('b','2')
INSERT INTO #t VALUES('c','2')
INSERT INTO #t VALUES('c','3')
--第二個關系組
INSERT INTO #t VALUES('e','4')
INSERT INTO #t VALUES('e','5')
--第三個關系組
INSERT INTO #t VALUES('f','6')
希望的查詢結果：
a 組1
b 組1
c 組1
e 組2
f 組3
uj5u.com熱心網友回復：
組1組2組3是怎么判斷的？uj5u.com熱心網友回復：
看關系，p欄位使用gp欄位構建的關系。abc是鏈式關系。類似近親遠親都算親戚。只要是親戚就分在一組
uj5u.com熱心網友回復：
abc是連式關系，ef也是啊uj5u.com熱心網友回復：
ef沒有關系的
uj5u.com熱心網友回復：
;with haveRelation
as (
Select a.p as p,1 as gp
From #t a
join #t b on a.gp=b.gp and a.p<>b.p
group by a.p
),
noRelation
as (
Select p,max(gp) as gp
From (Select * From #t Where p not in (Select p From haveRelation)) a
Group By p
)
Select * From haveRelation
union
Select * From noRelation
uj5u.com熱心網友回復：
不對，對于以下資料，想把de分到一組，你會把abcde全分一組了。
INSERT INTO #t VALUES('a','1')
INSERT INTO #t VALUES('b','1')
INSERT INTO #t VALUES('b','2')
INSERT INTO #t VALUES('c','2')
INSERT INTO #t VALUES('c','3')
--第二個關系組
INSERT INTO #t VALUES('d','4')
INSERT INTO #t VALUES('e','4')
--第三個關系組
INSERT INTO #t VALUES('f','6')
uj5u.com熱心網友回復：
這樣有點復雜了，將你的資料結構轉成樹形關系再回圈遞回可以得到結果.
uj5u.com熱心網友回復：
不可以使用group by 來分組嗎uj5u.com熱心網友回復：
我也想知道，但貌似只有遞回可行？
uj5u.com熱心網友回復：
WITH CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SEQ FROM #T),
CTE_2
AS
(SELECT A.*,
CASE WHEN A.SEQ<ISNULL(B.SEQ,C.SEQ) AND A.SEQ<ISNULL(C.SEQ,B.SEQ) THEN 0
WHEN ISNULL(B.SEQ,C.SEQ)<A.SEQ AND ISNULL(B.SEQ,C.SEQ)<ISNULL(C.SEQ,B.SEQ) THEN ISNULL(B.SEQ,C.SEQ)
ELSE ISNULL(ISNULL(C.SEQ,B.SEQ),0) END AS PARENT_ID
FROM CTE_1 A
LEFT JOIN CTE_1 B ON A.P=B.P AND A.SEQ<>B.SEQ
LEFT JOIN CTE_1 C ON A.GP=C.GP AND A.SEQ<>C.SEQ),
CTE_3
AS
(SELECT *,P AS GROUP_ID FROM CTE_2 WHERE PARENT_ID=0
UNION ALL
SELECT A.*,B.GROUP_ID
FROM CTE_2 A
JOIN CTE_3 B ON A.PARENT_ID=B.SEQ)
SELECT GROUP_ID,P
FROM CTE_3
GROUP BY GROUP_ID,P
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109032.html
標籤：疑難問題