日期:2014-05-16 浏览次数:20593 次
if not object_id('tb') is null
drop table tb
Go
Create table tb([name] nvarchar(3),[score] int)
Insert tb
select N'张三',213 union all
select N'张三',32 union all
select N'李四',32 union all
select N'李四',54 union all
select N'李四',321 union all
select N'王小三',2 union all
select N'王小三',32 union all
select N'王小三',32
Go
;with tmp1
as
(
select rownum=row_number()over(partition by [name] order by getdate()),
*
from tb
),tmp2
as(
select b.name,
row_number()over(partition by b.name order by (getdate()))rownum,
null col1,
null col2
from master..spt_values a
,
(select [name],
count(*)+1 px
from tb
group by [Name])b
where a.type='P' and number<b.px )
select a.name,
a.Score
from tmp2 b left join tmp1 a
on a.rownum=b.rownum and a.name=b.name
/*
name Score
---- -----------
王小三 2
王小三 32
王小三 32
NULL NULL
李四 32
李四 54
李四 321
NULL NULL
张三 213
张三 32
NULL NULL
(11 row(s) affected)
*/