求每一天的Voltage的最大、最小值及其发生时间
数据如下:时间 Voltage
2004-4-23 0:14:00 221
2004-4-23 0:28:00 238
2004-4-23 0:42:00 210
2004-4-23 0:56:00 258
2004-4-23 1:14:00 198
2004-4-23 1:28:00 208
2004-4-23 1:42:00 225
2004-4-23 1:56:00 221
2004-4-24 0:14:00 248
2004-4-24 0:28:00 230
2004-4-24 0:42:00 228
2004-4-24 0:56:00 221
2004-4-24 1:14:00 225
2004-4-24 1:28:00 211
2004-4-24 1:42:00 213
2004-4-23 1:56:00 206
数据如下:时间 Voltage
2004-4-23 0:14:00 221
2004-4-23 0:28:00 238
2004-4-23 0:42:00 210
2004-4-23 0:56:00 258
2004-4-23 1:14:00 198
2004-4-23 1:28:00 208
2004-4-23 1:42:00 225
2004-4-23 1:56:00 221
2004-4-24 0:14:00 248
2004-4-24 0:28:00 230
2004-4-24 0:42:00 228
2004-4-24 0:56:00 221
2004-4-24 1:14:00 225
2004-4-24 1:28:00 211
2004-4-24 1:42:00 213
2004-4-23 1:56:00 206
最小值: select min voltage time from tble group by time
group by convert(char(20),时间,120)
select max(voltage) from 表
where 时间 between 日期+' 00:00:00' and 日期+' 23:59:59'
select top 1 时间,Voltage from 表名 orderby Voltage DESC求最小值及其时间
select top 1 时间,Voltage from 表名 orderby Voltage ASC
select top 1 时间,Voltage from 表名 order by Voltage DESC求最小值及其时间
select top 1 时间,Voltage from 表名 order by Voltage ASC
最小:select min(voltage), time from table group by time
最大值或最小值可能不止一条数据
(select max(Voltage) from orders)
union
select Voltage,时间 from orders where Voltage=
(select min(Voltage) from orders)
group by convert(char(10),时间,120)
注意,是每一天
而每一天中还有很多的行啊
dt: 时间;vol:Voltage;tbl:表名
...
select a.* from tbl a,
(select max(vol) as maxv, min(vol) as minv, Left(dt, CharIndex(':', dt)-4) as bdt from tbl group by Left(dt, CharIndex(':', dt)-4)) b
where a.vol in (b.maxv, b.minv) and Left(a.dt, CharIndex(':', a.dt)-4) = b.bdt
order by b.bdt, a.vol desc
union
select min (voltage), time from tble group by convert(char(8),time,112)
select max (voltage), time from tble group by convert(char(8),time,112),time
union
select min (voltage), time from tble group by convert(char(8),time,112),time
from test a group by left(convert(varchar,thetime,112),10)
建议用临时表
Left(dt, CharIndex(':', dt)-4)-> Convert(Char(10), dt, 20)
select min(voltage),max(voltage),时间 from 表
group by convert(char(10),时间,120),时间