sqlserver 2000 有一个表,表结构为:
RID(设备的ID),datTime(时间), dat1(数据),dat2(数据)
001 2011-02-25 01:01 34.4 3
001 2011-02-25 01:02 45 2
002 2011-02-25 01:01 56 3
002 2011-02-25 01:02 55 2 问题是:如何用一条语句得到:dat1的最大值,dat1最大值出现的时间,dat1的最小值,dat1最小值出现的时间,dat2最大值,dat2最大值出现的时间,dat2最小值,dat2最小值出现的时间
如果最大值有>2条,只取一条就可以.请高手帮忙.谢谢
RID(设备的ID),datTime(时间), dat1(数据),dat2(数据)
001 2011-02-25 01:01 34.4 3
001 2011-02-25 01:02 45 2
002 2011-02-25 01:01 56 3
002 2011-02-25 01:02 55 2 问题是:如何用一条语句得到:dat1的最大值,dat1最大值出现的时间,dat1的最小值,dat1最小值出现的时间,dat2最大值,dat2最大值出现的时间,dat2最小值,dat2最小值出现的时间
如果最大值有>2条,只取一条就可以.请高手帮忙.谢谢
select case when max(dat1) then datTime as maxdat1Time from ....
group by RID
group by RID
union all select * from (select top 1 dat1,datetime1 from sb2 order by dat1 asc) v2
union all select * from (select top 1 dat2,datetime1 from sb2 order by dat2 desc) v3
union all select * from (select top 1 dat2,datetime1 from sb2 order by dat2 asc) v4
结果是:
RID 日期 dat1最大值 dat1最大值时间 dat1最小值 dat1最小值时间 dat2.。
select t1.id,t1.r,t1.d1 maxd1,t1.t maxd1time,t2.d1 mind1,t2.t mind1time,t3.d2 maxd2,t3.t maxd2time,t4.d2 mind2,t4.t mind2time
from
(select tb1.id,tb1.r,max(t) t,tb1.d1 from tb tb1,(select id,r,max(d1) d1 from tb group by id,r) tb2 where tb1.id=tb2.id and tb1.r=tb2.r and tb1.d1=tb2.d1 group by tb1.id,tb1.r,tb1.d1) t1,
(select tb1.id,tb1.r,max(t) t,tb1.d1 from tb tb1,(select id,r,min(d1) d1 from tb group by id,r) tb2 where tb1.id=tb2.id and tb1.r=tb2.r and tb1.d1=tb2.d1 group by tb1.id,tb1.r,tb1.d1) t2,
(select tb1.id,tb1.r,max(t) t,tb1.d2 from tb tb1,(select id,r,max(d2) d2 from tb group by id,r) tb2 where tb1.id=tb2.id and tb1.r=tb2.r and tb1.d2=tb2.d2 group by tb1.id,tb1.r,tb1.d2) t3,
(select tb1.id,tb1.r,max(t) t,tb1.d2 from tb tb1,(select id,r,min(d2) d2 from tb group by id,r) tb2 where tb1.id=tb2.id and tb1.r=tb2.r and tb1.d2=tb2.d2 group by tb1.id,tb1.r,tb1.d2) t4
where t1.id=t2.id and t1.id=t3.id and t1.id=t4.id and t1.r=t2.r and t1.r=t3.r and t1.r=t4.r
SELECT * FROM
(SELECT TOP 1 datTime, dat1 FROM testTB a ORDER BY dat1 DESC),
(SELECT TOP 1 datTime, dat1 FROM testTB b ORDER BY dat1),
(SELECT TOP 1 datTime, dat2 FROM testTB a ORDER BY dat2 DESC),
(SELECT TOP 1 datTime, dat2 FROM testTB a ORDER BY dat2)
(select top 1 dat1 as maxD1,datTime as maxT1 from tableA where dat1 =(select max(dat1) from tableA)) A,
(select top 1 dat1 as minD1,datTime as minT1 from tableA where dat1 =(select min(dat1) from tableA)) B,
(select top 1 dat2 as maxD2,datTime as maxT2 from tableA where dat2 =(select max(dat2) from tableA)) C,
(select top 1 dat2 as minD2,datTime as minT2 from tableA where dat2 =(select min(dat2) from tableA)) D) S
SELECT * FROM
(SELECT TOP 1 datTime, dat1 FROM testTB ORDER BY dat1 DESC),
(SELECT TOP 1 datTime, dat1 FROM testTB ORDER BY dat1),
(SELECT TOP 1 datTime, dat2 FROM testTB ORDER BY dat2 DESC),
(SELECT TOP 1 datTime, dat2 FROM testTB ORDER BY dat2)