问题描述
有A 一张表
A表如下
iD customer date price weight moneys
01 张三 2009-07-01 10 20 200
02 张三 2009-07-02 20 20 400
实现功能为一个日报表
我要查询的结果是显示今天的产量sum(weight)以及销售金额sum(moneys),并且显示昨天的产量、金额
结果如下:
customer 昨天weight 今天weight 昨天金额 今天金额
张三 20 20 200 400
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--
=========================================
-- -----------t_mac 小编-------------
-- -希望有天成为大虾----
-- =========================================
IF OBJECT_ID ( ' tb ' ) IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(iD int ,customer varchar ( 10 ), date datetime , price int , weight int , moneys int )
go
insert tb SELECT
01 , ' 张三 ' , ' 2009-7-31 ' , 10 , 20 , 200 UNION ALL SELECT
02 , ' 张三 ' , ' 2009-08-01 ' , 20 , 20 , 400
go
select
customer,
昨天weight = MAX ( case when DATEDIFF (dd,date, getdate ()) = 1 then weight else 0 end ),
今天weight = MAX ( case when DATEDIFF (dd,date, getdate ()) = 0 then weight else 0 end ),
昨天金额 = MAX ( case when DATEDIFF (dd,date, getdate ()) = 1 then moneys else 0 end ),
今天金额 = MAX ( case when DATEDIFF (dd,date, getdate ()) = 0 then moneys else 0 end )
from tb
group by customer
go
/* ------------
ustomer 昨天weight 今天weight 昨天金额 今天金额
---------- ----------- ----------- ----------- -----------
张三 20 20 200 400
------- */
-- -----------t_mac 小编-------------
-- -希望有天成为大虾----
-- =========================================
IF OBJECT_ID ( ' tb ' ) IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(iD int ,customer varchar ( 10 ), date datetime , price int , weight int , moneys int )
go
insert tb SELECT
01 , ' 张三 ' , ' 2009-7-31 ' , 10 , 20 , 200 UNION ALL SELECT
02 , ' 张三 ' , ' 2009-08-01 ' , 20 , 20 , 400
go
select
customer,
昨天weight = MAX ( case when DATEDIFF (dd,date, getdate ()) = 1 then weight else 0 end ),
今天weight = MAX ( case when DATEDIFF (dd,date, getdate ()) = 0 then weight else 0 end ),
昨天金额 = MAX ( case when DATEDIFF (dd,date, getdate ()) = 1 then moneys else 0 end ),
今天金额 = MAX ( case when DATEDIFF (dd,date, getdate ()) = 0 then moneys else 0 end )
from tb
group by customer
go
/* ------------
ustomer 昨天weight 今天weight 昨天金额 今天金额
---------- ----------- ----------- ----------- -----------
张三 20 20 200 400
------- */