现在假设有个表t1
id pid itime
--------------------------
1 2 2009-1-1
2 4 2009-1-2
3 5 2009-1-3
4 7 2009-1-4
5 4 2009-1-5
6 2 2009-1-5
7 6 2009-1-6
--------------------------
如果查询得到不重复的PID且按itime倒序排序。
id pid itime
--------------------------
1 2 2009-1-1
2 4 2009-1-2
3 5 2009-1-3
4 7 2009-1-4
5 4 2009-1-5
6 2 2009-1-5
7 6 2009-1-6
--------------------------
如果查询得到不重复的PID且按itime倒序排序。
比如
1 2 2009-1-1
6 2 2009-1-5
这两个DISTINCT后你说要我取哪个?
from t1
order by itime desc
create table #t (id int ,pid int,item datetime)
insert into #t values ( 1, 2 , '2009-1-1')
insert into #t values (2 , 4 , '2009-1-2' )
insert into #t values (3 , 5 , '2009-1-3' )
insert into #t values (4 , 7 , '2009-1-4' )
insert into #t values (5 , 4 , '2009-1-5' )
insert into #t values (6 , 2 , '2009-1-5' )
insert into #t values (7 ,6, '2009-1-6' )select * from #t where pid in (select pid from #t group by pid having count(pid)=1) order by item desc
--drop table #t
select * from #t where pid in (select pid from #t group by pid having count(pid)=1) order by item desc
如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中。
用mysql测的
insert into @t values(1,2,'2009-1-1')
insert into @t values(2,4,'2009-1-2')
insert into @t values(3,5,'2009-1-3')
insert into @t values(4,7,'2009-1-4')
insert into @t values(5,4,'2009-1-5')
insert into @t values(6,2,'2009-1-5')
insert into @t values(7,6,'2009-1-6')select distinct(pid) as pid,max(id) as id,max(itime) as itime from @t group by pid order by itime测试一下