在一张视图里搞定:
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号店'