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 Employeeid CardNo DevNo CardRemain ConsumeMoney ConsumeDate<br>1 20 00023 1 180.5 2.5 2008-01-01 09:00:03<br>1 21 00043 2 134.5 4.5 2008-01-01 09:01:04<br>1 20 00023 2 178.0 2.5 2008-01-01 12:10:07<br>1 20 00023 1 175.5 2.5 2008-01-02 08:30:01<br><br>查询结果如下:<br>Employeeid CardNo ConsumeDate Meal1Money Meal2Money Meal3Money<br>20 00023 2008-01-01 2.5 2.5 0<br>20 00023 2008-01-02 2.5 0 0<br>21 00043 2008-01-01 2.5 0 0<br><br>不知我的意思各位明白没有?请各位帮忙想想办法,我想了好久都没想出来.