select hdid,(select max(vtime) from tablename where hdid=a.hdid) as maxvtime,vtime as maxV_Time from tablename a where v=(select max(v) from tablename where hdid=a.hdid) 这是最直观的
--上面的少了最小值以及v的最大值 select hdid,(select max(vtime) from tablename where hdid=a.hdid) as maxvtime,(select min(vtime) from tablename where hdid=a.hdid) as minvtime,v as maxV,vtime as maxV_Time from tablename a where v=(select max(v) from tablename where hdid=a.hdid)
换思路好一些select a.hdid,max(vtime) as maxvtime,min(vtime) as minvtime,max(v) as maxV,max(case when a.v=t.maxV then vtime else null end) as maxV_Time from tablename a,(select hdid,max(v) as maxV from tablename group by hdid) as t where a.hdid=t.hdid
晕,上面写漏了select a.hdid,max(vtime) as maxvtime,min(vtime) as minvtime,max(v) as maxV,max(case when a.v=t.maxV then vtime else null end) as maxV_Time from tablename a,(select hdid,max(v) as maxV from tablename group by hdid) as t where a.hdid=t.hdid group by a.hdid
from tablename a
where v=(select max(v) from tablename where hdid=a.hdid)
这是最直观的
select hdid,(select max(vtime) from tablename where hdid=a.hdid) as maxvtime,(select min(vtime) from tablename where hdid=a.hdid) as minvtime,v as maxV,vtime as maxV_Time
from tablename a
where v=(select max(v) from tablename where hdid=a.hdid)
from tablename a,(select hdid,max(v) as maxV from tablename group by hdid) as t
where a.hdid=t.hdid
from tablename a,(select hdid,max(v) as maxV from tablename group by hdid) as t
where a.hdid=t.hdid
group by a.hdid