求一SQL语句,请各位高手来想想该如何写这一语句.(100分)

T

taibu

Unregistered / Unconfirmed
GUEST, unregistred user!
有一表结构如下:<br>Create Table ConsumeRecord(Autoid int identity(1,1) Primary key, Employeeid int,<br> CardNo Varchar(20),<br> DevNo int,<br> CardRemain Decimal(6,2),<br> ConsumeMoney Decimal(6,2),<br> ConsumeDate DateTime,<br> Operator Varchar(20),<br> OperatorDate DateTime,<br> Memo Varchar(50))<br>Go<br>我想从这个表中,查语结果表的结构如下:<br>Employeeid,CardNo,ConsumeDate,Meal1Money,Meal2Money,Meal3Money<br>ConsumeDate保存的是日期,如2008-01-24,同一卡号,同一天只能有一条记录.<br>查询要求:<br>当ConsumeRecord表的ConsumeDate在08:00:00~11:59:59时,把记录的ConsumeMoney累加保存到Meal1Money中,当ConsumeDate在12:00:00~16:59:59时,把记录的ConsumeMoney累加到Meal2Money中,当ConsumeDate在17:00:00时,把记录的ConsumeMoney累加到Meal3Money中.<br><br>比如:ConsumeRecord有以下记录:<br>Autoid &nbsp;Employeeid &nbsp;CardNo &nbsp;DevNo CardRemain &nbsp;ConsumeMoney ConsumeDate<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp;00023 &nbsp; &nbsp; 1 &nbsp; &nbsp; 180.5 &nbsp; &nbsp; &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp;2008-01-01 09:00:03<br>1 &nbsp; &nbsp; &nbsp; &nbsp;21 &nbsp; &nbsp; &nbsp; &nbsp;00043 &nbsp; &nbsp; 2 &nbsp; &nbsp; 134.5 &nbsp; &nbsp; &nbsp; 4.5 &nbsp; &nbsp; &nbsp; &nbsp;2008-01-01 09:01:04<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp;00023 &nbsp; &nbsp; 2 &nbsp; &nbsp; 178.0 &nbsp; &nbsp; &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp;2008-01-01 12:10:07<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp;00023 &nbsp; &nbsp; 1 &nbsp; &nbsp; 175.5 &nbsp; &nbsp; &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp;2008-01-02 08:30:01<br><br>查询结果如下:<br>Employeeid &nbsp;CardNo &nbsp;ConsumeDate &nbsp;Meal1Money &nbsp;Meal2Money &nbsp;Meal3Money<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00023 &nbsp; 2008-01-01 &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp; 0<br>20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00023 &nbsp; 2008-01-02 &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0<br>21 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00043 &nbsp; 2008-01-01 &nbsp; 2.5 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0<br><br>不知我的意思各位明白没有?请各位帮忙想想办法,我想了好久都没想出来.
 
高手都去哪了,怎么这么久都不见一个人回复?
 
select employeeid,cardno,convert(nchar,consumedate,110),<br>case datepart(hour,consumedate) between 8 and 11 then sum(ConsumeMoney) end as Meal1Money,<br>case datepart(hour,consumedate) between 12 and 16 then sum(ConsumeMoney) end as Meal2Money,<br>case datepart(hour,consumedate) between 17 and 23 then sum(ConsumeMoney) end as Meal3Money,<br>from ConsumeRecord<br>group by employeeid,cardno,convert(nchar,consumedate,110)<br><br>思路就是这样了.
 
select &nbsp;Employeeid, CardNo,cast(left(ConsumeDate,10) as datetime)as ConsumeDate,<br>sum( case when datepart(hh,consumedate)&gt;=8 and &nbsp;datepart(hh,consumedate)&lt;12 then ConsumeMoney else 0 end) as Meal1Money,<br>sum( case when datepart(hh,consumedate)&gt;=12 and &nbsp;datepart(hh,consumedate)&lt;17 then ConsumeMoney else 0 end) as Meal2Money,<br>sum( case when datepart(hh,consumedate)&gt;=17 then ConsumeMoney else 0 end) as Meal3Money<br>&nbsp; from ConsumeRecord group by Employeeid, CardNo,cast(left(ConsumeDate,10) as datetime)
 
先谢谢两位,我试下.
 
kingsonchan的错误提示为:<br>服务器: 消息 156,级别 15,状态 1,行 2<br>在关键字 'between' 附近有语法错误。<br><br>laiaseven的方法能执行.我再试!
 
本来我去调试去了,没想到,这么多人跑我前面去了.<br>楼上的方法应该都可以,不过我补充一下.<br>在SELECT 中,和GROUP BY 中应该用<br>convert(nchar,getdate(),23) &nbsp;这样的到的才你上面写的标准短日期格式.<br>截取字符串有时候会不准确,例如,2007-1-1
 
to wuyongzhen<br>看你的账号,我们应该同姓哦,和我平时用的账号相差一点点.哈哈<br>谢谢各位了,很想多给一点分,可惜倾我全部都没多少了,谢了!
 
顶部