Question

高手进 这个T-Sql语句怎么写？

数据库TEMP表：thedatetime(datetime),thedatetimeflag(datetime),thevalue(int),thevol(int),thevolflag(int);

thedatetimeflag和thedatetime一样，只不过不包含秒，意思就是只有年月日时分；

我想写一个查询语句根据thedatetimeflag分组获取到thevol的和，这个我知道怎么写，select sum(thevol),max(thevalue),min(thevalue) from [TEMP] group by thedatetimeflag,但是我想取得每个组中开始的thevalue和结束的thevalue当然聚合函数中没有start(thevalue)和end(thevalue);怎么办？别说要用循环重新写逻辑，循环效率低。我就想用group by来写，这样效率高点

Answer 1

select sum(thevol),(select top 1 thevalue from [TEMP] where thedatetimeflag=x.thedatetimeflag order by id desc) as maxval,(select top 1 thevalue from [TEMP] where thedatetimeflag=x.thedatetimeflag order by id asc) as minval from [TEMP] x group by thedatetimeflag