select 门店编码,商品编码,
当日销量=sum(case when datediff(day,销售日期,'2007-12-01')=0 then xssl else 0 end),
前1天销量=sum(case when datediff(day,销售日期,'2007-12-01')=1 then xssl else 0 end),
前2天销量=sum(case when datediff(day,销售日期,'2007-12-01')=2 then xssl else 0 end),
前3天销量=sum(case when datediff(day,销售日期,'2007-12-01')=3 then xssl else 0 end),
上月销量=sum(case when datediff(month,销售日期,'2007-12-01')=1 then xssl else 0 end)
from tb
group by 门店编码,商品编码
当日销量=sum(case when datediff(day,销售日期,'2007-12-01')=0 then xssl else 0 end),
前1天销量=sum(case when datediff(day,销售日期,'2007-12-01')=1 then xssl else 0 end),
前2天销量=sum(case when datediff(day,销售日期,'2007-12-01')=2 then xssl else 0 end),
前3天销量=sum(case when datediff(day,销售日期,'2007-12-01')=3 then xssl else 0 end),
上月销量=sum(case when datediff(month,销售日期,'2007-12-01')=1 then xssl else 0 end)
from tb
group by 门店编码,商品编码
解决方案 »
- 是否可以使用触发器将对整个数据库执行的Sql语句保存为文本文件?
- 关于C#+ASP采用三层结果调用存储过程的问题,在线等——————————————急——————————————————————
- 菜鸟 请教大师 一个简单问题 下班结帖!
- 关于主键的一个超简单问题
- 一道面试题求解
- 无法启动MSSQLSERVER服务,initerrlog: 无法打开错误日志文件 ''。操作系统错误 = 3(系统找不到指定的路径。)。
- 请教如何取得 “年”+“月”,没有日的时间段?解决马上结贴..
- 加上n个工作日后的日期
- bcp备份500M的数据库,为什么dump备份有4G?
- 解释一条sql语句
- SQL 传值问题
- SELECT * FROM KJKMB WHERE KMMC=' 杨勇-恒台' 结果不符!是不是有BUG啊?我快急疯了...请帮忙!
--上月可以理解为自然月和按当前日期的一个月,我这里按自然月理解
declare @Today datetime
declare @ProMonthFirstDay datetime
declare @ProMonthLastDay datetimeset @Today=convert(varchar(10),getdate(),120)
set @ProMonthFirstDay=convert(varchar(7),dateadd(month,-1,@Today),120)+'-01'
set @ProMonthLastDay=dateadd(month,1,@ProMonthFirstDay)
select mdid as 门店编码,
spid as 商品编码,
sum(case when date>=@Today and Date<dateadd(day,1,@Today) then xssl else 0 end) as 当日销量,
sum(case when date>=dateadd(day,-1,@Today) and Date<@Today then xssl else 0 end) as 前1天销量,
sum(case when date>=dateadd(day,-2,@Today) and Date<dateadd(day,-1,@Today) then xssl else 0 end) as 前2天销量,
sum(case when date>=dateadd(day,-3,@Today) and Date<dateadd(day,-2,@Today) then xssl else 0 end) as 前3天销量,
sum(case when date>=@ProMonthFirstDay and Date<@ProMonthLastDay then xssl else 0 end) as 上月销量
from [table]
where Date>=@ProMonthFirstDay
and Date<dateadd(day,1,@Today)
group by mdid,spid