高手请进！sql server在查询出的结果增加空白行！

[yesterday2000]

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'

[Shawn]

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

/*
nid	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
*/

[yesterday2000]

数据库是SQL SERVER 2000

[Shawn]

--方法2

;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
),
cte3 AS
(
	SELECT newrowno = ROW_NUMBER() OVER(PARTITION BY id ORDER BY rowno),id FROM cte2
)
SELECT n.id, n.name
FROM cte3 m
LEFT JOIN cte n
ON m.id = n.id
	AND m.newrowno = n.rowno
ORDER BY m.id, m.newrowno

[Shawn]

--2000如下:
SELECT
	nid = (CASE rowno WHEN 0 THEN id END),
	name = (CASE name WHEN 'empty' THEN NULL ELSE name END)
FROM
(
	SELECT rowno = 0, id, name FROM cs
	UNION ALL
	SELECT resultcount=m.resultcount-n.number, m.id, 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 number,nullname='empty' FROM (SELECT number=0 UNION ALL SELECT 1 UNION ALL SELECT 2) t
		WHERE number < M.addcount
	) N
) t
ORDER BY id, rowno

/*
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
*/

[lzw_0736]

--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

[Shawn]

不过，这个东西，放在数据库里来处理，似乎不太合适。如果可以，还是用程序来实现吧。

[yesterday2000]

Shawn 我在执行你的语句的时候出错！！ 服务器: 消息 170，级别 15，状态 1，行 11 第 11 行: 'APPLY' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 14 第 14 行: 'N' 附近有语法错误。

[yesterday2000]

软件早就不开发了，N年前的客户，突然找到我，让我帮他们实现这个功能，但是我已经不是这个圈子里了，都是好朋友，没办法硬着头皮应下了！！

[yesterday2000]

Shawn 能不能把你的语句再改一下！报错!!!!

[Shawn]

引用 9 楼 yesterday2000 的回复:

Shawn 能不能把你的语句再改一下！报错!!!!

不好意思啊，楼主，2000没有APPLY的语法。把它改成INNER JOIN就好了。

--2000如下:
SELECT
    nid = (CASE rowno WHEN 0 THEN id END),
    name = (CASE name WHEN 'empty' THEN NULL ELSE name END)
FROM
(
    SELECT rowno = 0, id, name FROM cs
    UNION ALL
    SELECT resultcount=m.resultcount-n.number, m.id, 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
    INNER JOIN
    (
        SELECT number,nullname='empty' FROM (SELECT number=0 UNION ALL SELECT 1 UNION ALL SELECT 2) t
    ) N
		ON m.addcount > n.number
) t
ORDER BY id, rowno