我的 SQL Server 表如下所示：
id Distance a b Grp
--------------------------------
1 0.0000000000 100 114 NULL
2 0.1000000000 64 125 NULL
3 0.1000000000 88 100 NULL
4 0.1000000000 65 125 NULL
5 0.1000000000 63 64 NULL
6 0.1000000000 65 66 NULL
7 0.2000000000 63 66 NULL
8 0.2000000000 10 61 NULL
9 0.2000000000 19 61 NULL
10 0.2000000000 30 61 NULL
11 0.2000000000 10 65 NULL
12 0.2000000000 10 94 NULL
13 0.2000000000 19 65 NULL
14 0.2000000000 19 94 NULL
15 0.2000000000 30 94 NULL
16 0.2000000000 60 94 NULL
17 0.2000000000 61 94 NULL
該Grp列應填寫如下
第一個記錄
Grp是 1如果 a & b 的下一行的值在任何前一行中，那么它將采用第一行的
Grp值如果 a & b 的下一行的值不在任何前一行中，則
Grpvalue 將為max Grp 1如果記錄 id = 3 那么 b = 100 的值存在于前幾行中，它出現的第一個是 id = 1，
Grp = 1因此Grpid 3 的值為 1
這是我的表應該是這樣的：
id Distance a b Grp
--------------------------------
1 0.0000000000 100 114 1
2 0.1000000000 64 125 2
3 0.1000000000 88 100 1
4 0.1000000000 65 125 2
5 0.1000000000 63 64 2
6 0.1000000000 65 66 2
7 0.2000000000 63 66 2
8 0.2000000000 10 61 3
9 0.2000000000 19 61 3
10 0.2000000000 30 61 3
11 0.2000000000 10 65 2
12 0.2000000000 10 94 3
13 0.2000000000 19 65 2
14 0.2000000000 19 94 3
15 0.2000000000 30 94 3
16 0.2000000000 60 94 3
17 0.2000000000 61 94 3
我已經構建了這個運行良好的腳本，但它非常慢，有什么辦法可以讓它更好（沒有回圈）？
DECLARE @T AS TABLE
(
id int IDENTITY,
Distance decimal(18, 10),
a int,
b int,
Grp int
)
INSERT INTO @T(Distance, a, b)
SELECT Distance, a, b
FROM MyTable
ORDER BY Distance
UPDATE @T
SET Grp = 1
WHERE id = 1
DECLARE @i int = 2, @max int, @min int,
@grp int, @a int, @b int, @maxgrp int = 1
SELECT @max = MAX(id) FROM @T
WHILE @i <= @max
BEGIN
SELECT @a = a, @b = b
FROM @T
WHERE id = @i
SELECT @min = MIN(id)
FROM @T
WHERE id < @i AND a IN (@a, @b) OR b IN (@a, @b)
SELECT @grp = grp
FROM @T
WHERE id = @min
IF @grp IS NULL
BEGIN
SET @maxgrp = @maxgrp 1
SET @grp = @maxgrp
END
UPDATE @T
SET Grp = @grp
WHERE id = @i
SET @i = @i 1
END
SELECT * FROM @T
uj5u.com熱心網友回復：
使用遞回而不是回圈的答案。
- https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5410e95927e81d41f27f1d569b5d3cd3
首先，確定第一行出現的任何值（來自 a 或 b）...
CREATE TABLE #node(
row_id INT,
old_val INT,
new_val INT,
link_count INT
INDEX node_old_new CLUSTERED (link_count, old_val, new_val, row_id),
);
INSERT INTO
#node
SELECT
e.id, twinned.*, COUNT(*) OVER (PARTITION BY e.id)
FROM
#example AS e
CROSS APPLY
(
SELECT e.a AS old_val, e.b AS new_val
UNION ALL
SELECT e.b AS old_val, e.a AS new_val
)
AS twinned
WHERE
NOT EXISTS (
SELECT *
FROM #example AS lookup
WHERE twinned.new_val IN (lookup.a, lookup.b)
AND lookup.id < e.id
)
;
其中link_count = 2，雙方 a 并 b出現首次在此列上，這意味著該行會開始一個新的組。
哪里link_count = 1，new_val以前從未見過，但old_val一直存在。因此，一旦old_val分配了一個組，我們就可以將該組傳播到new_val。
這只是創建閉包表的樹遍歷。
WITH
closure AS
(
SELECT
new_val AS val,
DENSE_RANK() OVER (ORDER BY row_id) AS grp,
row_id AS row_id,
0 AS depth
FROM
#node
WHERE
link_count = 2
UNION ALL
SELECT
r.new_val,
c.grp,
r.row_id,
c.depth 1
FROM
closure AS c
INNER JOIN
#node AS r
ON r.old_val = c.val
AND r.link_count = 1
)
現在，對于aor 中的任何值，b我們可以在閉包表中查找該值的組。我們可能會得到兩個不同的組，一組來自仰視a，一組來自仰視b；因此，我們采用從最早的行分配的組。
SELECT
e.*, g.grp
FROM
#example e
CROSS APPLY
(
SELECT TOP 1
c.grp
FROM
#closure AS c
WHERE
c.val IN (e.a, e.b)
ORDER BY
c.row_id
)
AS g
ORDER BY
e.id
uj5u.com熱心網友回復：
如果只需要按Grp它分組，可以這樣簡化：
declare @t table (id int identity, Distance decimal(18,10)
, a int, b int, Grp int)
insert @t (Distance, a, b)
-- select Distance, a, b From MyTable order by Distance
values
(0.0,100,114),(0.1, 64,125),(0.1, 88,100),(0.1, 65,125),
(0.1, 63, 64),(0.1, 65, 66),(0.2, 63, 66),(0.2, 10, 61),
(0.2, 19, 61),(0.2, 30, 61),(0.2, 10, 65),(0.2, 10, 94),
(0.2, 19, 65),(0.2, 19, 94),(0.2, 30, 94),(0.2, 60, 94),
(0.2, 61, 94)
declare @i int, @d float, @a int, @b int, @g int
declare c1 cursor for select * from @t for update of Grp
open c1
fetch next from c1 into @i, @d, @a, @b, @g
update @t set Grp = 1 where current of c1
fetch next from c1 into @i, @d, @a, @b, @g
while @@fetch_status = 0
begin
update @t set Grp =
isNull((select top 1 (Grp)
from @t t2
where t2.id < @i
and (@a in (t2.a , t2.b)
or @b in (t2.a , t2.b)))
, @i)
where current of c1
fetch next from c1 into @i, @d, @a, @b, @g
end
close c1
deallocate c1
-- If you need consecutive Grp numbers ..
declare @u table (id int identity, Grp int)
insert @u (Grp)
select distinct Grp from @t order by Grp
update @t set Grp = u.id
from @t t
join @u u on (u.Grp = t.Grp and u.Grp<>u.id)
select * from @t
uj5u.com熱心網友回復：
以下查詢生成您想要的輸出。
WITH t3 as(
SELECT *
FROM
(SELECT id,Distance,a,b,rnk,
CASE WHEN rnk > 0 THEN NULL ELSE grp END AS Grp,
Row_Number() OVER(ORDER BY id) AS seq
FROM
(SELECT id,Distance,a,b,rnk,ROW_NUMBER() OVER(PARTITION BY rnk ORDER BY id) AS grp
FROM
(SELECT id,Distance,a,b,
ISNULL((SELECT top 1 id FROM tb s2 WHERE s2.id < s1.id AND (s2.a = s1.a OR s2.b = s1.b OR s2.b = s1.a OR s2.a = s1.b)),0) AS rnk
FROM tb s1) T) T) T
WHERE Grp > 0
)
SELECT id,Distance,a,b,min(grp)
FROM
(SELECT distinct *
FROM
(SELECT t1.id,t1.Distance,t1.a,t1.b,t3.grp
FROM
(SELECT id,Distance,a,b,grp,
ISNULL((SELECT top 1 id FROM tb s2 WHERE s2.id < s1.id AND (s2.a = s1.a OR s2.b = s1.b OR s2.b = s1.a OR s2.a = s1.b)),0) AS rnk
FROM tb s1) t1
JOIN tb t2 ON t1.rnk = t2.id
JOIN t3 ON
t1.a = t3.a OR t1.a = t3.b OR
t1.b = t3.b OR t1.b = t3.a OR
t2.b = t3.b OR t2.b = t3.a OR
t2.a = t3.a OR t2.a = t3.b) t1
UNION ALL
SELECT id,Distance,a,b,grp
FROM t3) T
GROUP BY id,Distance,a,b
ORDER BY id
db<>fiddle 中的演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374176.html
標籤：sql sql-server 查询语句 sql更新