select case px when 1 then col1 else '' end,col2 from (select row_number()over(partition by col1 order by getdate()),* from tb )t
try ;with tmp as (select *, rn=row_number()over(partition by col1 order by col2) from tb) select col1=case when rn=1 then col1 else '' end, col2 from tmp
if not object_id('tb') is null drop table tb Go Create table tb([col1] nvarchar(1),[col2] nvarchar(1)) Insert tb select N'一',N'二' union all select N'一',N'三' union all select N'一',N'四' Go select case when rn=1 then col1 else '' end col1, col2 from( select *, rn=(select count(*) from tb where [col1]=t.[col1] and [col2]<=t.[col2]) from tb t )t
SQL SERVER 2000 没有row_number 这样就可以了: declare @t table (col1 varchar(2),col2 varchar(2)) insert into @t select '一','二' union all select '一','三' union all select '一','四'select col1=case when (select count(*) from @t where col1=a.col1 and col2<=a.col2) =1 then col1 else '' end, col2 from @t a /* col1 col2 ---- ---- 一 二 三 四 */
foe SQL2000,create table h838831486 (col1 char(4), col2 char(4))insert into h838831486 select '一', '二' union all select '一', '三' union all select '一', '四' select identity(int,1,1) rn,* into #h838831486 from h838831486select case when rn=1 then col1 else '' end 'col1', col2 from #h838831486col1 col2 ---- ---- 一 二 三 四 (3 row(s) affected)
最好的方式加自动序列字段:sid int identity(1,1)if OBJECT_ID('tb') is not null drop table tb go create table tb (sid int identity(1,1),a varchar(50),b int) insert into tb values('A',100) insert into tb values('A',200) insert into tb values('C',200) insert into tb values('d',100) insert into tb values('A',500) insert into tb values('A',100) insert into tb values('C',300) insert into tb values('A',300) insert into tb values('C',200) --正确的SQL如下: select sid,a,b,(case when sid=(select min(sid) sid from tb where a=t.a) then a else '' end) a2 from tb t order by a,sid ------------ 执行结果: sid a b a2 ---------------------------- 1 A 100 A 2 A 200 5 A 500 6 A 100 8 A 300 3 C 200 C 7 C 300 9 C 200 4 d 100 d ---------------------------
case px when 1 then col1 else '' end,col2
from
(select row_number()over(partition by col1 order by getdate()),* from tb )t
;with tmp
as
(select *,
rn=row_number()over(partition by col1 order by col2)
from tb)
select col1=case when rn=1 then col1 else '' end,
col2
from tmp
drop table tb
Go
Create table tb([col1] nvarchar(1),[col2] nvarchar(1))
Insert tb
select N'一',N'二' union all
select N'一',N'三' union all
select N'一',N'四'
Go
select case when rn=1 then col1 else '' end col1,
col2
from(
select *,
rn=(select count(*)
from tb
where [col1]=t.[col1] and [col2]<=t.[col2])
from tb t
)t
这样就可以了:
declare @t table (col1 varchar(2),col2 varchar(2))
insert into @t
select '一','二' union all
select '一','三' union all
select '一','四'select col1=case when (select count(*) from @t
where col1=a.col1 and col2<=a.col2) =1 then col1 else '' end,
col2 from @t a
/*
col1 col2
---- ----
一 二
三
四
*/
(col1 char(4), col2 char(4))insert into h838831486
select '一', '二' union all
select '一', '三' union all
select '一', '四'
select identity(int,1,1) rn,*
into #h838831486
from h838831486select case when rn=1 then col1 else '' end 'col1',
col2
from #h838831486col1 col2
---- ----
一 二
三
四 (3 row(s) affected)
drop table tb
go
create table tb (sid int identity(1,1),a varchar(50),b int)
insert into tb values('A',100)
insert into tb values('A',200)
insert into tb values('C',200)
insert into tb values('d',100)
insert into tb values('A',500)
insert into tb values('A',100)
insert into tb values('C',300)
insert into tb values('A',300)
insert into tb values('C',200)
--正确的SQL如下:
select sid,a,b,(case when sid=(select min(sid) sid from tb where a=t.a) then a else '' end) a2
from tb t order by a,sid
------------
执行结果:
sid a b a2
----------------------------
1 A 100 A
2 A 200
5 A 500
6 A 100
8 A 300
3 C 200 C
7 C 300
9 C 200
4 d 100 d
---------------------------