我想知道是否有比我已經使用 SQL Server 更好的解決方案。
我的桌子看起來像這樣
RepID ICD9_L1
----------------
1 AB
1 NJ
1 KL
2 TH
2 KL
2 EE
2 SR
3 AB
3 SR
....
95871 PY
95871 EE
95871 AB
95871 VX
我想要一個所有代碼的串列和一個二進制字串表示，如果它存在于該 RepID 中或不存在
所以
AB : 1（表示它為 RepID 1 退出）、0（表示它對于 RepID 2 不存在）、1（表示它為 RepID 3 退出）、...、1（表示它為 RepID 95871 退出)
NJ : 1, 0, 0, ..., 0
KL : 1, 1, 0, ..., 0
TH : 0, 1, 0, ..., 0
我使用外觀構建它，但它非常非常慢
declare @T as table (ICD9_L1 varchar(100), StrExist varchar(max))
insert into @T (ICD9_L1)
select distinct ICD9_L1 FROM Diag
declare @i int = 0
declare @x varchar(max) = ''
declare @code varchar(10)
DECLARE db_cursor CURSOR FOR
select ICD9_L1 FROM @T
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @code
WHILE @@FETCH_STATUS = 0
BEGIN
while @i < 70000
begin
set @i = @i 1
if exists(select top 1 * FROM Diag where RepID = @i AND ICD9_L1 = @code)
begin
set @x = @x ',1'
end
else
begin
set @x = @x ',0'
end
end
update @T set StrExist = @x where ICD9_L1 = @code
set @x = ''
FETCH NEXT FROM db_cursor INTO @code
END
CLOSE db_cursor
DEALLOCATE db_cursor
uj5u.com熱心網友回復：
使用string_agg()連接0,1
使用計數/數字表生成左連接表的 ID 串列。您也可以使用遞回 CTE 動態生成一個。
查詢：
with
-- generate a list of IDs
numbers as
(
select n = 1
union all
select n = n 1
from numbers
where n < 70000
),
codes as
(
select distinct code = ICD9_L1
from Diag
)
select c.code,
string_agg((case when d.RepID is not null then '1' else '0' end), ',')
within group (order by n.n)
from codes c
cross join numbers n
left join Diag d on c.code = d.ICD9_L1
and n.n = d.RepID
group by c.code
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/454763.html
上一篇：SQL組通過忽略通配符“%”