select avg_value ,max_value ,max_value_time=(select value_time from 表 where value=a.max_value) ,min_value ,min_value_time=(select value_time from 表 where value=a.min_value) from( select avg_value=avg(value) ,max_value=max(value) ,min_value=min(value) from 表)a
select avg(value) as avg_value,max(value) as max_value, max(value_time) as max_value_time, min(value) as min_value, min(value_time) as min_value_time from 表
select avg(value) as avg_value,max(value) as max_value,max(value_time) as max_value_time,min(value) as min_value,min(value_time) as min_value_time from table这样可以吗?
select avg([value]) as avg_value , max([value]) as max_value , max(value_time) as max_value_time , min([value]) as min_value , min(value_time) as min_value_time from aa
应该是max_value对应的 value_time
select avb_value=(select avg(value) from table), max_value=(select max(value) fromt table), max_value_time=(select max(value_time) from table), min_value_time=(select min(value_time) from table
select value=3, value_time=Convert(smalldatetime,'2004-01-01') into #a union select 10, '2004-02-02' union select 17, '2004-05-11'select avg_value=avg(value), max_value=max(value), max_value_time= max(value_time), min_value=min(value), min_value_time=min(value_time) from #a drop table #a
--测试数据 declare @a table(value int,value_time datetime)insert @a select 3,'2004-01-01' union all select 10,'2004-02-02' union all select 17,'2004-05-11'--查询 select avg(value) avg_value, max(value) max_value, (select value_time from @a where value = max(b.value)) max_value_time, min(value) min_value, (select value_time from @a where value = min(b.value)) min_value_time from @a as b
,max_value
,max_value_time=(select value_time from 表 where value=a.max_value)
,min_value
,min_value_time=(select value_time from 表 where value=a.min_value)
from(
select avg_value=avg(value)
,max_value=max(value)
,min_value=min(value)
from 表)a
max(value_time) as max_value_time,
min(value) as min_value,
min(value_time) as min_value_time
from 表
min_value_time 同上
max_value_time,min(value) as min_value,min(value_time) as min_value_time
from table这样可以吗?
max([value]) as max_value ,
max(value_time) as max_value_time ,
min([value]) as min_value ,
min(value_time) as min_value_time
from aa
max_value=(select max(value) fromt table),
max_value_time=(select max(value_time) from table),
min_value_time=(select min(value_time) from table
union
select 10, '2004-02-02'
union
select 17, '2004-05-11'select avg_value=avg(value),
max_value=max(value), max_value_time= max(value_time),
min_value=min(value), min_value_time=min(value_time)
from #a drop table #a
declare @a table(value int,value_time datetime)insert @a
select 3,'2004-01-01'
union all
select 10,'2004-02-02'
union all
select 17,'2004-05-11'--查询
select avg(value) avg_value,
max(value) max_value,
(select value_time from @a where value = max(b.value)) max_value_time,
min(value) min_value,
(select value_time from @a where value = min(b.value)) min_value_time
from @a as b