关于合计的问题,待解决啊
SELECT
CASE WHEN GROUPING(s.Month)=1 THEN '合计' ELSE cast(YEAR AS varchar)+'年'+cast(s.MONTH AS varchar)+'月' END ,
tmp.quarter
FROM dbo.s_SalesBudget s
INNER JOIN dbo.p_tmp tmp ON s.Month = tmp.month
WHERE YEAR = '2009'
GROUP BY s.Month ,
YEAR ,
tmp.quarter
with rollup
结果
有很多重复和Null的数据,这个要怎么解决啊,求解啊大神们
[解决办法]
select * from (
SELECT
CASE WHEN GROUPING(s.Month)=1 THEN '合计' ELSE cast(YEAR AS varchar)+'年'+cast(s.MONTH AS varchar)+'月' END yearmonth,
tmp.quarter
FROM dbo.s_SalesBudget s
INNER JOIN dbo.p_tmp tmp ON s.Month = tmp.month
WHERE YEAR = '2009'
)a where quarter is not null
GROUP BY s.Month ,
YEAR ,
tmp.quarter
with rollup
SELECT
CASE WHEN GROUPING(s.Month)=1 THEN '合计' ELSE cast(YEAR AS varchar)+'年'+cast(s.MONTH AS varchar)+'月' END ,
isnull(tmp.quarter,0)
FROM dbo.s_SalesBudget s
INNER JOIN dbo.p_tmp tmp ON s.Month = tmp.month
WHERE YEAR = '2009'
GROUP BY s.Month ,
YEAR ,
tmp.quarter
with rollup
SELECT CASE WHEN GROUPING(tmp.quarter) <> 1 THEN CAST(tmp.quarter as varchar)+'季度小计'
ELSE CAST(s.Year AS VARCHAR) + '年' + CAST(s.Month AS VARCHAR)
+ '月'
END ,
tmp.quarter
FROM dbo.s_SalesBudget s
INNER JOIN dbo.p_tmp tmp ON s.Month = tmp.month
WHERE YEAR = '2009'
GROUP BY
YEAR ,
tmp.quarter,s.Month
WITH ROLLUP
having GROUPING(year) = 0