表名advertice字段
adid, title, salerid
1 aa 1
2 bb 2
3 cc 3
4 dd 2
5 ee 4
6 ff 2
salerid有三次重复现在查询 adid,title,salerid,怎么控制只显示一个重复的saleridadid, title, salerid
1 aa 1
2 bb 2
3 cc 3
5 ee 4
adid, title, salerid
1 aa 1
2 bb 2
3 cc 3
4 dd 2
5 ee 4
6 ff 2
salerid有三次重复现在查询 adid,title,salerid,怎么控制只显示一个重复的saleridadid, title, salerid
1 aa 1
2 bb 2
3 cc 3
5 ee 4
from advertice A
inner join
(
select min(adid) adid,salerid
from advertice group by salerid
) B
on A.adid=B.adid
insert into cs1 select 'aa',1
union all select 'bb',2
union all select 'cc',3
union all select 'dd',2
union all select 'ee',4
union all select 'ff',2select * from cs1 a where adid in(select top 1 adid from cs1 where a.salerid=salerid)
select * from cs1 a where 1>(select count(1) from cs1 where salerid=a. salerid and adid>a.adid)
select * from cs1 a where 1>(select count(1) from cs1 where salerid=a. salerid and adid<a.adid)
这个好用.谢谢各位!!
通过这个刚明白group by的意思
谢谢大家了
select distinct salerid from advertice
insert @advertice
select 1, 'aa' , 1 union all
select 2, 'bb' , 2 union all
select 3 , 'cc' , 3 union all
select 4 , 'dd' , 2 union all
select 5 , 'ee' , 4 union all
select 6, ' ff', 2 select distinct adid =(select top 1 adid from @advertice where adid<=a.adid and salerid=a.salerid ),
title=(select top 1 title from @advertice where adid<=a.adid and salerid=a.salerid ),
salerid=(select top 1 salerid from @advertice where adid<=a.adid and salerid=a.salerid )
from @advertice a
结果为:(所影响的行数为 6 行)adid title salerid
----------- ----- -----------
1 aa 1
2 bb 2
3 cc 3
5 ee 4(所影响的行数为 4 行)
Select A.* From advertice A
Where Not Exists (Select 1 From advertice Where A.salerid = salerid And A.adid > adid)
Or Select Distinct A.* From advertice A
Where Adid In (Select Min(adid) From advertice Where A.salerid = salerid)