34,588
社区成员
发帖
与我相关
我的任务
分享
select 'A' c1 , 'B' c2 , 1 c3 into # union all
select 'A' , 'B' , 2 union all
select 'A' , 'B' , 3 union all
select 'A' , 'C' , 1 union all
select 'A' , 'C' , 2 union all
select 'A' , 'C' , 3 union all
select 'A' , 'D' , 1 union all
select 'A' , 'D' , 2 union all
select 'A' , 'D' , 3
select c1,c2,c3=(select count(0) from (select distinct c1,c2 from #) v where v.c1=t.c1 and v.c2<=t.c2)
from ( select distinct c1,c2 from #) t
drop table #
/*
c1 c2 c3
---- ---- -----------
A B 1
A C 2
A D 3
(3 row(s) affected)
*/
--sql server 2005的写法.
create table tb(col1 varchar(10) , col2 varchar(10), col3 varchar(10))
insert into tb values('A' , 'B' , 1 )
insert into tb values('A' , 'B' , 2 )
insert into tb values('A' , 'B' , 3 )
insert into tb values('A' , 'C' , 1 )
insert into tb values('A' , 'C' , 2 )
insert into tb values('A' , 'C' , 3 )
insert into tb values('A' , 'D' , 1 )
insert into tb values('A' , 'D' , 2 )
insert into tb values('A' , 'D' , 3 )
go
select col1,col2,id = row_number() over(order by col1 , col2) from
(
select distinct col1,col2 from tb
) t
drop table tb
/*
col1 col2 id
---------- ---------- -----------
A B 1
A C 2
A D 3
(所影响的行数为 3 行)
*/
declare @tb table (col1 varchar(10),col2 varchar(10),col3 int)
insert into @tb select 'a','b',1
insert into @tb select 'a','b',2
insert into @tb select 'a','b',3
insert into @tb select 'a','c',1
insert into @tb select 'a','c',2
insert into @tb select 'a','c',3
insert into @tb select 'a','d',1
insert into @tb select 'a','d',2
insert into @tb select 'a','d',3
select col1 , col2 , id = row_number() over(order by col1 , col2) from
(
select distinct col1 , col2 from @tb
) t
--sql server 2000的写法.
create table tb(col1 varchar(10) , col2 varchar(10), col3 varchar(10))
insert into tb values('A' , 'B' , 1 )
insert into tb values('A' , 'B' , 2 )
insert into tb values('A' , 'B' , 3 )
insert into tb values('A' , 'C' , 1 )
insert into tb values('A' , 'C' , 2 )
insert into tb values('A' , 'C' , 3 )
insert into tb values('A' , 'D' , 1 )
insert into tb values('A' , 'D' , 2 )
insert into tb values('A' , 'D' , 3 )
go
select col1,col2,id = identity(int , 1, 1) into tmp from
(
select distinct col1,col2 from tb
) t
select * from tmp
drop table tb,tmp
/*
col1 col2 id
---------- ---------- -----------
A B 1
A C 2
A D 3
(所影响的行数为 3 行)
*/
--2000
select distinct col1,col2, col3 = identity(int,1,1) into tmp from tb
select * from tmp
--2005
select col1 , col2 , id = rownumber() over(order by col1 , col2) from
(
select distinct col1 , col2 from tb
) t
--col2有大小关系时
select
distinct
col1,
col2,
col3=(select count(1) from T where col1=a.col1 and col2!>a.col2)
from
T a
insert into @tb select 'a','c',1
insert into @tb select 'a','c',2
insert into @tb select 'a','c',3
insert into @tb select 'a','d',1
insert into @tb select 'a','d',2
insert into @tb select 'a','d',3
select col1,col2,row_number() over(order by col2)
from @tb group by col1,col2