id cc dd
1 1 1
2 1 2
3 2 1
4 2 2
5 1 1
6 2 2如何写一个SQL语句,选出cc列不重复的,并且相同的值只出现一次?
不要写存储过程啥的,就写“一个”SQL语句,可以写得很长cc dd(此处dd值任意)
1 1
2 2
1 1 1
2 1 2
3 2 1
4 2 2
5 1 1
6 2 2如何写一个SQL语句,选出cc列不重复的,并且相同的值只出现一次?
不要写存储过程啥的,就写“一个”SQL语句,可以写得很长cc dd(此处dd值任意)
1 1
2 2
insert @t
select 1, 1, 1 union all
select 2, 1, 2 union all
select 3, 2, 1 union all
select 4, 2, 2 union all
select 5, 1, 1 union all
select 6, 2, 2select cc,dd from @t a
where not exists(select 1 from @t where cc = a.cc and id > a.id)
--或
select cc,dd from @t a
where not exists(select 1 from @t where cc = a.cc and id < a.id)
select a.cc,a.dd from @t a
inner join (select cc,max(dd) as dd,max(id) as id from @t group by cc) b
on a.id = b.id
--或
select a.cc,a.dd from @t a
inner join (select cc,max(dd) as dd,min(id) as id from @t group by cc) b
on a.id = b.id
id name
11 aaaa
11 bbbb
11 cccc
22 dddd
22 eeee
22 ffff
如何将表中的相同id号的第一条记录取出来?即:
id name
11 aaaa
22 ddddselect id1=identity(int,1,1),* into #t from a
go
select id,name from #t where id1 in(select min(id1) from #t group by id)select * from a b
where name=(select top 1 name from a where id=b.id)
1 1 1
2 1 2
3 2 1
4 2 2
5 1 1
6 2 2select * from a,b
where id = (select top 1 id from a where cc = b.cc)
inner join (select cc,max(dd) as dd,min(id) as id from @t group by cc) b
on a.id = b.id这里select cc,max(dd) as dd,min(id) as id from @t group by cc,是不是已经获取了需要的结果了,是否没必要在inner join?
这行代码中max(dd) as dd是多余的.虽然结果与需求一致,但只是巧合,也就是说正巧最大dd和最小id在同一行.这行代码是求cc列相同行的最大dd和最小id,而不是求cc列相同时id列最小的行,因为"行"的概念是指行中可能还有其它N多个列(如ff,gg,ii,jj,kk....),当找到这行时连同其它列一起列出,而上面这行代码就无法列出其它所有行.
from TableName
group by cc
select cc,max(dd) as dd from @t group by cc
这样搜索出来同一行的的各个列属性值,并不一定是原先表中对应的行顺便提一下,发现SQL Server和My SQL下的distinct,操作的机制不一样,有兴趣的朋友自己可以回去试一下:)光荣结帖