--字面意思 select * from sampleImage a where not exists(select 1 from sampleImage where sphID=a.sphID and imgID<>a.imgID) and imgID<>(select max(imgID) from sampleImage )
--字面意思 select * from sampleImage a where (select count(1) from sampleImage where sphID=a.sphID)>1 and imgID <>(select max(imgID) from sampleImage )
select * from sampleimage where imgid <> (select max(imgid) from sampleimage) group by imgid
select min(imgID),sphID from sampleImage group by sphID having count(sphID)=1
set nocount on declare @sampleImage table(imgID int identity(1,1),sphID int) insert @sampleImage select 123 insert @sampleImage select 456 insert @sampleImage select 123 insert @sampleImage select 789 insert @sampleImage select 147 insert @sampleImage select 789 --取imgID最大 select * from @sampleImage s where not exists(select 1 from @sampleImage where sphID=s.sphID and imgID>s.imgID) --取imgID最小 select * from @sampleImage s where not exists(select 1 from @sampleImage where sphID=s.sphID and imgID<s.imgID) /* imgID sphID ----------- ----------- 2 456 3 123 5 147 6 789imgID sphID ----------- ----------- 1 123 2 456 4 789 5 147 */
表:sampleImage 字段:imgID,sphID(有重复) 如何查询出sphID不重复的结果,并且不包含最大imgID的记录??????????????? select a.sphid,b.imgid from (select distinct sphid from sampleImage) a,sampleImage b where a.sphid=b.sphid and esists(select 1 from sampleImage c wehre c.imgid>bimgid)
參照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
select * from sampleImage a where not exists(select 1 from sampleImage where sphID=a.sphID and imgID<>a.imgID) and imgID<>(select max(imgID) from sampleImage )
select * from sampleImage a where (select count(1) from sampleImage where sphID=a.sphID)>1 and imgID <>(select max(imgID) from sampleImage )
字段:imgID,sphID(有重复) 如何查询出sphID不重复的结果,并且不包含最大imgID的记录--sphID不重复是指distinct ?
-------------------
imgID sphID
1 1
2 1
3 1
4 2
5 2像需求 "如何查询出sphID不重复的结果"那这种情况如果查询不重复sphID,那么结果应该显示哪些记录呢?
1 1
4 2
还是
2 1
4 2
呢?????
from sampleimage
where imgid <> (select max(imgid) from sampleimage)
group by imgid
select min(imgID),sphID
from sampleImage
group by sphID
having count(sphID)=1
declare @sampleImage table(imgID int identity(1,1),sphID int)
insert @sampleImage select 123
insert @sampleImage select 456
insert @sampleImage select 123
insert @sampleImage select 789
insert @sampleImage select 147
insert @sampleImage select 789
--取imgID最大
select * from @sampleImage s where not exists(select 1 from @sampleImage where sphID=s.sphID and imgID>s.imgID)
--取imgID最小
select * from @sampleImage s where not exists(select 1 from @sampleImage where sphID=s.sphID and imgID<s.imgID)
/*
imgID sphID
----------- -----------
2 456
3 123
5 147
6 789imgID sphID
----------- -----------
1 123
2 456
4 789
5 147
*/
字段:imgID,sphID(有重复) 如何查询出sphID不重复的结果,并且不包含最大imgID的记录???????????????
select a.sphid,b.imgid from (select distinct sphid from sampleImage) a,sampleImage b where a.sphid=b.sphid and esists(select 1 from sampleImage c wehre c.imgid>bimgid)
-------------------
imgID sphID
1 1
2 1
3 1
4 2
5 2 像需求 "如何查询出sphID不重复的结果" 那这种情况如果查询不重复sphID,那么结果应该显示哪些记录呢?
1 1
4 2
还是
2 1
4 2
呢?????
7楼说得对,楼主你这个需求本身就是矛盾的