高手请进！sql server在查询出的结果增加空白行！
ID相同的为一组，每组不足三行的补足三行，保证每组都能被三整除！！
实列如下！
id name
1 aa
1 a
1 aa
2 aq
2 aq
3 a
3 a
3 v
3 d
4 c
要的结果
id name
1 aa
1 a
1 aa
2 aq
2 aq
null null
3 a
3 a
3 v
3 d
null null
null null
4 c
null null
null null
create table cs (id char (2),name char (10))
insert into cs
select'1' , 'aa'
union all
select'1' , 'a'
union all
select'1' , 'aa'
union all
select'2' , 'aq'
union all
select'2' , 'aq'
union all
select'3' , 'a'
union all
select'3' , 'a'
union all
select'3' , 'v'
union all
select'3' , 'd'
union all
select'4' , 'c' 结果 空白行 sql?server
[解决办法]
create table cs (id char (2),name char (10))
insert into cs
select'1' , 'aa'
union all
select'1' , 'a'
union all
select'1' , 'aa'
union all
select'2' , 'aq'
union all
select'2' , 'aq'
union all
select'3' , 'a'
union all
select'3' , 'a'
union all
select'3' , 'v'
union all
select'3' , 'd'
union all
select'4' , 'c'
;WITH cte AS
(
SELECT rowno =ROW_NUMBER() OVER(PARTITION BY id ORDER BY GETDATE()), * FROM cs
),
cte1 AS
(
SELECT m.id, m.resultcount, n.nullname FROM
(
SELECT id, addcount = (3-COUNT(1)%3)%3, resultcount=COUNT(1)+(3-COUNT(1)%3)%3
FROM cs
GROUP BY id
) M
CROSS APPLY
(
SELECT nullname='empty' FROM master..spt_values
WHERE type = 'p'
AND number < M.addcount
) N
),
cte2 AS
(
SELECT rowno, id, name FROM cte
UNION ALL
SELECT resultcount, id, nullname FROM cte1
)
SELECT
nid=CASE name WHEN 'empty' THEN NULL ELSE id END,
name = CASE name WHEN 'empty' THEN NULL ELSE name END
FROM cte2
ORDER BY id, rowno
/*
nidname
1 aa
1 a
1 aa
2 aq
2 aq
NULLNULL
3 a
3 a
3 v
3 d
NULLNULL
NULLNULL
4 c
NULLNULL
NULLNULL
*/
--SQL2000
select id,count(*) as qty into #cu1 from cs group by id
select *,3-case when qty%3=0 then 3 else qty%3 end add_qty into #cu2 from #cu1
select *,id id2 into #cu3 from cs
union all
select null,null,a.id
from #cu2 a
inner join master..spt_values b on b.number between 1 and a.add_qty
where b.type='P'
select id,name from #cu3 order by id2,id desc
