继续问题,再次麻烦kingsonchan,laiaseven及wuyongzhen:<<<求一SQL语句,请各位高手来想想该如何写这一语句.&

有一表结构如下:<br>Create Table ConsumeRecord(Autoid int identity(1,1) Primary key, &nbsp;Employeeid int,<br>&nbsp;CardNo Varchar(20),<br>&nbsp;DevNo int,<br>&nbsp;CardRemain Decimal(6,2),<br>&nbsp;ConsumeMoney Decimal(6,2),<br>&nbsp;ConsumeDate DateTime,<br>&nbsp;Operator Varchar(20),<br>&nbsp;OperatorDate DateTime,<br>&nbsp;Memo Varchar(50))<br>Go<br>我想从这个表中,查语结果表的结构如下:<br>Employeeid,CardNo,ConsumeDate,Meal1Money,Meal2Money,Meal3Money<br>ConsumeDate保存的是日期,如2008-01-24,同一卡号,同一天只能有一条记录.<br>查询要求:<br>当ConsumeRecord表的ConsumeDate在08:10:00~11:29:58时,把记录的ConsumeMoney累加保存到Meal1Money中,当ConsumeDate在11:29:59~16:59:58时,把记录的ConsumeMoney累加到Meal2Money中,当ConsumeDate在16:59:59~08:09:59(第二天)时,把记录的ConsumeMoney累加到Meal3Money中.<br><br>该问题在http://www.delphibbs.com/delphibbs/dispq.asp?lid=3869730中问过,现在的要求是时间界限的改变,不知这样改后,如何去比较这个时间,还请各位高手来帮帮忙!再次谢谢各位!!!

再次请kingsonchan,laiaseven及wuyongzhen三位高手来帮忙!

这样比较麻烦，有一个思路是这样：去掉日期信息来比较。<br>在昨天的SQL里，以第一个case为例，改为<br>case when dateadd(hour,datepart(hour,att_datetime),0)+dateadd(minute,datepart(minute,att_datetime),0) between '08:10' and '11:29' then ConsumeMoney else 0 end<br><br>如果你要加上秒数的判断，则再+datepart second好了（我觉得大可不必）<br>这种方法是可以的，不过我觉得效率上不好，计算多了些。<br>昨天没调试，写的语句有问题，不好意思了。

select Employeeid,CardNo,aDate,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(case when aTime&gt;='08:10:00' and aTime&lt;='11:29:58'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; then ConsumeMoney<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) as Meal1Money,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(case when aTime&gt;='11:29:59' and aTime&lt;='16:59:58'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; then ConsumeMoney<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) as Meal2Money,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(case when (aTime&gt;='16:59:59' and aTime&lt;='23:59:59')<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;or (aTime&gt;='00:00:00' and &nbsp;aTime&lt;='08:09:59')<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; then ConsumeMoney<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) as Meal3Money &nbsp; <br>from<br>&nbsp; &nbsp; &nbsp;(select *,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when convert(varchar,ConsumeDate,108)&gt;='00:00:00' <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and convert(varchar,ConsumeDate,108)&lt;='08:09:59'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; then Cast(convert(char(10),ConsumeDate,120) as DateTime) -1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else Cast(convert(char(10),ConsumeDate,120) as DateTime)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end as aDate,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;convert(varchar,ConsumeDate,108) aTime<br>&nbsp; &nbsp; &nbsp; from Temp1<br>&nbsp; &nbsp; &nbsp t1<br>where 1=1<br>group by Employeeid,CardNo,aDate

可以考虑用字符串的形式组合sql语句

多人接受答案了。