SQL得到如下记录集。如何让NA重复的记录中用NULL值表示?谢谢!NA CJ KM
--------------------
a 2 a2
a 1 a1
a 3 a3
b 1 b1
b 3 b3
b 2 b2
b 4 b4
b 5 b5 想得到以下格式:NA CJ KM
--------------------
a 2 a2
null 1 a1
null 3 a3
b 1 b1
null 3 b3
null 2 b2
null 4 b4
null 5 b5
--------------------
a 2 a2
a 1 a1
a 3 a3
b 1 b1
b 3 b3
b 2 b2
b 4 b4
b 5 b5 想得到以下格式:NA CJ KM
--------------------
a 2 a2
null 1 a1
null 3 a3
b 1 b1
null 3 b3
null 2 b2
null 4 b4
null 5 b5
go
create table [tb]([NA] varchar(1),[CJ] int,[KM] varchar(2))
insert [tb]
select 'a',2,'a2' union all
select 'a',1,'a1' union all
select 'a',3,'a3' union all
select 'b',1,'b1' union all
select 'b',3,'b3' union all
select 'b',2,'b2' union all
select 'b',4,'b4' union all
select 'b',5,'b5'
goselect case when rn=1 then na end as na,cj,km
from
(select rn=row_number() over(partition by na order by getdate()),* from tb)t/**
na cj km
---- ----------- ----
a 2 a2
NULL 1 a1
NULL 3 a3
b 1 b1
NULL 3 b3
NULL 2 b2
NULL 4 b4
NULL 5 b5(8 行受影响)
**/
请问SQL 2000 中如何实现呢?
select *,tid=identity(int,1,1) into # from tb
goselect
case when
(select count(1) from # where na=t.na and tid<=t.tid)=1
then na end as na,cj,km
from
# t/**
na cj km
---- ----------- ----
a 2 a2
NULL 1 a1
NULL 3 a3
b 1 b1
NULL 3 b3
NULL 2 b2
NULL 4 b4
NULL 5 b5(8 行受影响)
**/