这是我所知道的最好方法。如果有人知道更好的解决方案,请发帖:
我已经把你的桌子命名了 sal
DECLARE @id INT
, @max INT
, @dep INT
, @all VARCHAR(255)
SELECT @id = 1
, @max = MAX(id)
FROM sal
SELECT * INTO #tmp FROM sal
WHILE (1=1)
BEGIN
SELECT @dep = dept
FROM #tmp
WHERE id = @id
IF @dep IS NULL
BEGIN
SELECT @id = @id + 1
IF @id > @max
BREAK
ELSE
CONTINUE
END
UPDATE #tmp
SET @all = @all + ',' + CONVERT(VARCHAR, id)
WHERE dept = @dep
--remove last comma
select @all = RIGHT(@all, LEN(@all)-1)
DELETE #tmp
WHERE dept = @dep
-- selecting the output. insert into table if you want
SELECT @dep, @all
SELECT @dep = NULL
, @all = NULL
SELECT @id = @id + 1
IF @id > @max
BREAK
-- fail safe
IF @id > 100
BREAK
END
drop table #tmp