sql 按照天环比_sql同比、环比代码放在一起。。。 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌...

在一张视图里搞定：

SELECT

c.[注册单位],

c.[消费门店] AS [消费门店],

convert(varchar(10), c.消费时间, 23) AS [消费时间],

c.[销售金额] AS [今天销售金额],

d.销售金额 AS [昨天销售金额],

e.[销售金额] AS[上年今天销售金额],

case when d.销售金额 is null or d.销售金额=0 then '无对比数据' else cast(cast((isnull(c.销售金额, 0)-isnull(d.销售金额,0))*100/isnull(d.销售金额, 0) as decimal(10,2)) as varchar(50))+'%' end AS [环比增长],

case when e.销售金额 is null or e.销售金额=0 then '无对比数据' else cast(cast((isnull(c.销售金额, 0)-isnull(e.销售金额,0))*100/isnull(e.销售金额, 0) as decimal(10,2)) as varchar(50))+'%' end AS [同比增长]

FROM

dbo.[门店日销售] AS c

LEFT JOIN (select a.消费门店 as 消费门店, a.销售金额 as 销售金额, a.消费时间 as ls消费时间, b.消费时间 as 消费时间 from 门店日销售 a join 门店日销售 b on a.消费时间=DateAdd(DAY, -1, b.消费时间) and a.消费门店=b.消费门店) AS d ON  c.[消费时间]= d.[消费时间] and c.[消费门店] = d.消费门店

LEFT JOIN (select f.消费门店 as 消费门店, f.销售金额 as 销售金额, f.消费时间 as ls消费时间, g.消费时间 as 消费时间 from 门店日销售 f join 门店日销售 g on f.消费时间=DateAdd(year, -1, g.消费时间) and f.消费门店=g.消费门店) AS e ON d.[消费时间]= e.[消费时间] and d.[消费门店] = e.消费门店

WHERE

c.[注册单位] = 'rjsyt' and c.[消费门店] = '6号店'