将如下规律的集合
A , B , 1
A , B , 2
A , B , 3
A , C , 1
A , C , 2
A , C , 3
A , D , 1
A , D , 2
A , D , 3
想转化成:
A, B, 1
A, C, 2
A, D, 3
sql怎么写呢?
A , B , 1
A , B , 2
A , B , 3
A , C , 1
A , C , 2
A , C , 3
A , D , 1
A , D , 2
A , D , 3
想转化成:
A, B, 1
A, C, 2
A, D, 3
sql怎么写呢?
解决方案 »
- 如何优化此SQL语句?
- SQL 显示 百分比率的 问题 百思不知如何解?
- 求SQL语句,急!在线等,立即给分!
- 如果参数中带有image类型的数据,如果用执行 字符串的方式来执行插入操作,该怎么办?
- sys.database_files与sys.master_files在TEMPDB中查询结果不一致
- 为什么我创建的视图,查询分析器中打开时,标题栏上写着 readonly?如果尝试修改某条数据,发现确实是只读的?但是用sql语句确又可以修改
- 一条简单的sql语句如何表达10050分相送
- 怎么把一行插入到表的前边而不后边
- sql server trigger
- 请问如何在用sql语句创建一个access数据表时设置默认值?
- 求一个多表查询的语句:5个表分两组,谢谢
- 一个很简单的问题,刚学触发器,大家给看看
from
t a
where
not exists(select 1 from t where col1=a.col1 and col2=a.col2 and col3>a.col3)
col1,
col2,
min(col3)
from t
group by col1, col2
from tb group by col1,col2
insert into @tb select 'a','c',2
insert into @tb select 'a','c',3insert into @tb select 'a','d',1
insert into @tb select 'a','d',2
insert into @tb select 'a','d',3select col1,col2,row_number() over(order by col2)
from @tb group by col1,col2col1 col2 (无列名)
a b 1
a c 2
a d 3
select
distinct
col1,
col2,
col3=(select count(1) from T where col1=a.col1 and col2!>a.col2)
from
T a
A , B , 1
A , B , 2
A , B , 3
A , C , 1
A , C , 2
A , C , 3
A , D , 1
A , D , 2
A , D , 3
想转化成:
A, B, 1
A, C, 2
A, D, 3
sql怎么写呢?--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
第2列col2没有大小关系时,用临时表实现效率好一点,用语句嵌套效率低
A, B, 3
A, C, 3
A, D, 3
--col2有大小关系时
select
distinct
col1,
col2,
col3=(select count(1) from T where col1=a.col1 and col2!>a.col2)
from
T a
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 )
goselect col1,col2,id = identity(int , 1, 1) into tmp from
(
select distinct col1,col2 from tb
) tselect * from tmpdrop table tb,tmp/*
col1 col2 id
---------- ---------- -----------
A B 1
A C 2
A D 3(所影响的行数为 3 行)
*/
insert into @tb select 'a','b',1
insert into @tb select 'a','b',2
insert into @tb select 'a','b',3insert into @tb select 'a','c',1
insert into @tb select 'a','c',2
insert into @tb select 'a','c',3insert into @tb select 'a','d',1
insert into @tb select 'a','d',2
insert into @tb select 'a','d',3select col1 , col2 , id = row_number() over(order by col1 , col2) from
(
select distinct col1 , col2 from @tb
) t都行
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 )
goselect col1,col2,id = row_number() over(order by col1 , col2) from
(
select distinct col1,col2 from tb
) tdrop table tb/*
col1 col2 id
---------- ---------- -----------
A B 1
A C 2
A D 3(所影响的行数为 3 行)
*/
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)
*/