一条SQL语句中算日销售额和月销售额
刚刚做项目的时候用到的
用户表:用户ID,用户名,余额
流水表:时间,用户ID,用户名,类型(0充值,1消费),变更金额
现在要查每个用户的日销售额和月销售额,本来最简单的方法是先把所有用户查出来,然后再c#代码中for循环中再select sum(changemoney) from liushui where userid=? 的
现在想试试看一个SQL语句 就查出来,经搜索得到如下 SQL语句:
WITH DailySales AS( select [user].id,[user].username,[user].balance, case when sum([liushui].changemoney) is NULL then 0 else sum([liushui].changemoney) end as ri_xse from [user] left join [liushui] on [user].id=[liushui].userid and [liushui].type=1 and [liushui].createtime between '2017-07-11 00:00:00' and '2017-07-11 23:59:59' group by [user].id,[user].username,[user].balance ) ,MonthSales AS( select [user].id,[user].username,[user].balance, case when sum([liushui].changemoney) is NULL then 0 else sum([liushui].changemoney) end as yue_xse from [user] left join [liushui] on [user].id=[liushui].userid and [liushui].type=1 and [liushui].createtime between '2017-07-01 00:00:00' and '2017-07-31 23:59:59' group by [user].id,[user].username,[user].balance ) select d.id,d.username,d.balance,d.ri_xse,m.yue_xse from DailySales D inner join MonthSales M on D.id = M.id
结果如下图:
查出来了,网友建议最好流水表分表,如一月一表,要不然的话以后流水表会很大很大很大。。。
先这么弄着先吧。。