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'
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 ENDFROM cte2 ORDER BY id, rowno /* nid name1 aa 1 a 1 aa 2 aq 2 aq NULL NULL3 a 3 a 3 v 3 d NULL NULLNULL NULL4 c NULL NULLNULL NULL*/
--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 allselect 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