想要时间维度不同，数据汇总跟着维度走，怎么改sql?

目前数据量比较大，所以觉得这个方案可以尝试，但是我不知道怎么改sql

目前的sql：

SELECT

a.BillDate 日期,

YEAR (a.BillDate) 年份,

MONTH (a.BillDate) 月份,

DAY (a.BillDate) 日,

a.ShopID 门店ID,

b.BrandName 品牌,

b.HotelName 门店,

SUM (a.Amount) 实际营收,

c.[Value] 计划营收

FROM

[dbo].[DIM_POS_Shop] b

LEFT JOIN [dbo].[FACT_POS_Bill] a ON a.ShopID = b.ShopID

LEFT JOIN [dbo].[FACT_Target_Day] c ON c.ShopID = b.HotelID

where c.Items = '营收（万元）'

${if(type='date'," and CONVERT(varchar(100),a.BillDate,20) >= '" + 开始日期 + "' and CONVERT(varchar(100),a.BillDate,20)<='" + 结束日期 + "'", "")}

${if(type='month', " and cast(month(CONVERT(varchar(100),a.BillDate,20)) as int) = "+ 月 + " and year(CONVERT(varchar(100),a.BillDate,20)) = '" + 年+"'", "")}

${if(type='year', " and year(CONVERT(varchar(100),a.BillDate,20)) = '" + 年+"'", "")}

${if(len(品牌)==0,"","and b.BrandName in ('"+品牌+"')")}

${if(len(门店)==0,"","and b.HotelName in ('"+门店+"')")}

GROUP BY

a.ShopID,

a.BillDate,

b.BrandName,

b.HotelName,

c.[Value]

图一是按日维度的效果，是没有问题的：

图二我选择了按月维度，但是这个内容里面还是按天展示，我想要按月展示数据，数据汇总起来：

图三也是想要以年汇总：

FineReport 百煮味香 发布于 2022-8-25 14:43 （编辑于 2022-8-25 14:52）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

yzm339714Lv6中级互助
发布于2022-8-25 14:50（编辑于 2022-8-25 15:07）

GROUP BY

a.ShopID,

${if(type='date'," CONVERT(varchar(100),a.BillDate,20) " , "")}

${if(type='month', " CONVERT(varchar(7),a.BillDate,20)) ", "")}

${if(type='year', " CONVERT(varchar(4),a.BillDate,20)) " , "")}

b.BrandName,

b.HotelName,

c.[Value]

group by 改成这样，对应的 select 里面的billDate 字段也改成这样，不过select 里面记得加上名别名

SELECT

${if(type='date'," CONVERT(varchar(100),a.BillDate,20) as riqi " , "")}

${if(type='month', " CONVERT(varchar(7),a.BillDate,20)) as riqi ", "")}

${if(type='year', " CONVERT(varchar(4),a.BillDate,20)) as riqi " , "")}

a.ShopID 门店ID,

b.BrandName 品牌,

b.HotelName 门店,

SUM (a.Amount) 实际营收,

c.[Value] 计划营收

举报收起评论(5)

百煮味香(提问者) 我多补了一个图您再帮我看看呢？

2022-08-25 14:53

yzm339714 回复百煮味香(提问者) 那种太麻烦了，写的太长了，直接在帆软里面写的话，就会先根据判断来组装一个新的sql，然后再查询，也就改对应需要变动的列就行了

2022-08-25 15:06

yzm339714 回复百煮味香(提问者) 我也补充了select的怎么改日期,年份, 月份,日, 这三个都直接不要，用这个新的，就会自动变了

2022-08-25 15:08

百煮味香(提问者) 还有一点小问题我稍后再发一条问答您帮我看看

2022-08-25 15:21

yzm339714 回复百煮味香(提问者) 嗯嗯，好嘞

2022-08-25 15:27

runerLv7资深互助
发布于2022-8-25 15:03（编辑于 2022-8-25 15:15）

if(s_type = "date" ,

" SELECT

a.BillDate 日期,

YEAR (a.BillDate) 年份,

MONTH (a.BillDate) 月份,

DAY (a.BillDate) 日,

a.ShopID 门店ID,

b.BrandName 品牌,

b.HotelName 门店,

SUM (a.Amount) 实际营收,

c.[Value] 计划营收

FROM

[dbo].[DIM_POS_Shop] b

LEFT JOIN [dbo].[FACT_POS_Bill] a ON a.ShopID = b.ShopID

LEFT JOIN [dbo].[FACT_Target_Day] c ON c.ShopID = b.HotelID

where c.Items = '营收（万元）'

and CONVERT(varchar(100),a.BillDate,20) >= '" + 开始日期 + "'

and CONVERT(varchar(100),a.BillDate,20)<='" + 结束日期 + "'

"+if(len(品牌)==0,"","and b.BrandName in ('"+品牌+"')")+"

"+if(len(门店)==0,"","and b.HotelName in ('"+门店+"')")+"

GROUP BY

a.ShopID,

a.BillDate,

b.BrandName,

b.HotelName,

c.[Value]'

if(s_type = "month" ,

" SELECT

-- a.BillDate 日期,

YEAR (a.BillDate) 年份,

MONTH (a.BillDate) 月份,

-- DAY (a.BillDate) 日,

a.ShopID 门店ID,

b.BrandName 品牌,

b.HotelName 门店,

SUM (a.Amount) 实际营收,

c.[Value] 计划营收

FROM

[dbo].[DIM_POS_Shop] b

LEFT JOIN [dbo].[FACT_POS_Bill] a ON a.ShopID = b.ShopID

LEFT JOIN [dbo].[FACT_Target_Day] c ON c.ShopID = b.HotelID

where c.Items = '营收（万元）'

and cast(month(CONVERT(varchar(100),a.BillDate,20)) as int) = '"+ 月 + "'

and year(CONVERT(varchar(100),a.BillDate,20)) = '" + 年+"'

"+if(len(品牌)==0,"","and b.BrandName in ('"+品牌+"')")+"

"+if(len(门店)==0,"","and b.HotelName in ('"+门店+"')")+"

GROUP BY

a.ShopID,

YEAR (a.BillDate),

MONTH (a.BillDate),

b.BrandName,

b.HotelName,

c.[Value]'

if(s_type = "year" ,

" SELECT

-- a.BillDate 日期,

YEAR (a.BillDate) 年份,

-- MONTH (a.BillDate) 月份,

-- DAY (a.BillDate) 日,

a.ShopID 门店ID,

b.BrandName 品牌,

b.HotelName 门店,

SUM (a.Amount) 实际营收,

c.[Value] 计划营收

FROM

[dbo].[DIM_POS_Shop] b

LEFT JOIN [dbo].[FACT_POS_Bill] a ON a.ShopID = b.ShopID

LEFT JOIN [dbo].[FACT_Target_Day] c ON c.ShopID = b.HotelID

where c.Items = '营收（万元）'

and year(CONVERT(varchar(100),a.BillDate,20)) = '" + 年+"'

"+if(len(品牌)==0,"","and b.BrandName in ('"+品牌+"')")+"

"+if(len(门店)==0,"","and b.HotelName in ('"+门店+"')")+"

GROUP BY

a.ShopID,

YEAR (a.BillDate),

b.BrandName,

b.HotelName,

c.[Value]

,"")

)

}

----------

还得调整，日期字段的名称需要处理成一样的；我没有表，你先试试可以跑不

参数给个默认值

举报收起评论(1)

百煮味香(提问者) 您这个是完整的数据集不？这样写好像预览不了数据，然后没有字段出来可以拖入单元格

2022-08-25 15:05

3关注人数

292浏览人数

最后回答于：2022-8-25 15:15

活动推荐更多

热门课程更多

请选择关闭问题的原因

确定取消

提问

悬赏者（2）

撤回悬赏（2）

添加悬赏

请选择关闭问题的原因