SELECT ${if(type='date'," CONVERT(varchar(100),b.,20) as 日期,","")}${if(type='month', " CONVERT(varchar(7),b.,20) as 日期, ","")}${if(type='year', " CONVERT(varchar(4),b.,20) as 日期, ","")} a., a., sum(b.) 实际营收,sum(c.) 计划营收FROM ( SELECT t1.BrandName 品牌, t1.HotelName 门店, t1.ShopID 门店id, t1.HotelID 饭店id FROM . t1 WHERE t1.HotelName NOT IN ('', '测试门店') ) aLEFT JOIN ( SELECT t2.ShopID 门店ID, t2.BillDate 日期, SUM (t2.Amount) 实际营收 FROM . t2 WHERE t2.OrderStatus = '40' GROUP BY t2.ShopID, t2.BillDate) b ON a. = b.LEFT JOIN(SELECT t3.ShopID 门店id, t3.TargetDate 日期, SUM (t3.) 计划营收FROM . t3WHERE t3.Items = '营收(万元)'GROUP BY t3.ShopID, t3.TargetDate)con c.门店id=a.饭店idand b.=c.where 1=1${if(type='date'," and CONVERT(varchar(100),b.,20) >= '" + 开始日期 + "' and CONVERT(varchar(100),b.,20)<='" + 结束日期 + "'", "")}${if(type='month', " and cast(month(CONVERT(varchar(100),b.,20)) as int) = "+ 月 + " and year(CONVERT(varchar(100),b.,20)) = '" + 年+"'", "")}${if(type='year', " and year(CONVERT(varchar(100),b.,20)) = '" + 年+"'", "")}${if(len(品牌)==0,"","and a. in ('"+品牌+"')")}${if(len(门店)==0,"","and a. in ('"+门店+"')")}group by ${if(type='date'," CONVERT(varchar(100),b.[日期,20), ","")}${if(type='month', " CONVERT(varchar(7),b.[日期,20), ","")}${if(type='year', " CONVERT(varchar(4),b.[日期,20), ","")}