select a.BlockID as '卡号',
sum(a.SavingTotal)as '期初累计充值',
sum(a.ProductMoney+a.ServerMoney) as '本次充值',
sum (a.SavingTotal+a.ProductMoney+a.ServerMoney) as '充值合计',
sum(b.ProductMoney+b.ServiceItemMoney+b.ServiceCardMoney)as '本日消费',
sum(b.ProductMoney+b.ServiceItemMoney+b.ServiceCardMoney)as '本月累计消费',
sum(a.AlreadlyConsume)as '已消费金额',
sum(a.[Money]) as '卡内余额'
from tb_ConsumeCar as a
,tb_ClientConsume as b
where a.BlockID=b.BlockID
and ( b.SortType =0 or b.SortType =1 or b.SortType =2)
group by a.BlockID其中sum(b.ProductMoney+b.ServiceItemMoney+b.ServiceCardMoney)as '本日消费',
sum(b.ProductMoney+b.ServiceItemMoney+b.ServiceCardMoney)as '本月累计消费',
不知道如何嵌套时间,
或者各位有更好的办法?
--[YourDateField] 你的日期字段名
SUM(CASE WHEN CONVERT(VARCHAR(10),[YourDateField],120)=CONVERT(VARCHAR(10),GETDATE(),120)
THEN ISNULL(b.ProductMoney,0)+ISNULL(b.ServiceItemMoney,0)+ISNULL(b.ServiceCardMoney,0)
ELSE )AS '本日消费',
SUM(CASE WHEN CONVERT(VARCHAR(10),[YourDateField],120)=CONVERT(VARCHAR(10),GETDATE(),120)
THEN ISNULL(b.ProductMoney,0)+ISNULL(b.ServiceItemMoney,0)+ISNULL(b.ServiceCardMoney,0)
ELSE )AS '本月累计消费',
或者各位有更好的办法?
----
1:想嵌套时间,就在group by time
2:如果就你上面那些字段内容根本没有办法确定那个时间段,可以确定一个时间段就可以,在WHERE
但是,你设置了表tb_ClientConsume作为客户消费,你就可以在这个表上建立触发器(有三种触发方式),当insert一条记录时触发:更改其它表中记录!!!
例如,你要实现余额的减少就很容易了!
所以,建议看看触发器的相关内容!
或许理解错了,见谅……