34,590
社区成员
发帖
与我相关
我的任务
分享
--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
--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
--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
*/
;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
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
*/