select catalog=case grouping(catalog) when 1 then '合计' else case grouping(customid) when 1 then '小计' else catalog end end, customid=case grouping(customid) when 1 then NULL else customid end, regoin=regoin,price=sum(price) from test1 group by catalog,customid,regoin WITH ROLLUP having grouping(catalog)=1 or grouping(customid)=1 or grouping(regoin)=0 这样就可以了,试一下吧,至少我这边行的。 另外请问一下什么是水晶报表啊?
结果是对的,但是我这个查询不是很合理select 分类= case when 客户号 is null and 地区 is null and 分类 is null then '合计' when 客户号 is null and 地区 is null and 分类 is not null then '小计' else 分类 end,客户号,地区,价格 from ( select 分类,客户号,地区,价格=sum(价格) from test group by 分类, 客户号,地区 with rollup ) a where (客户号 is null and 地区 is null and 分类 is not null and 价格 is not null) or (客户号 is null and 地区 is null and 分类 is null and 价格 is not null) or (客户号 is not null and 地区 is not null and 分类 is not null and 价格 is not null)
else case grouping(customid) when 1 then '小计'
else catalog end end,
customid=case grouping(customid) when 1 then NULL else customid end,
regoin=regoin,price=sum(price)
from test1 group by catalog,customid,regoin WITH ROLLUP
having grouping(catalog)=1 or grouping(customid)=1 or grouping(regoin)=0
这样就可以了,试一下吧,至少我这边行的。
另外请问一下什么是水晶报表啊?
case
when 客户号 is null and 地区 is null and 分类 is null then '合计'
when 客户号 is null and 地区 is null and 分类 is not null then '小计'
else 分类
end,客户号,地区,价格
from (
select
分类,客户号,地区,价格=sum(价格)
from test
group by 分类, 客户号,地区
with rollup
) a
where
(客户号 is null and 地区 is null and 分类 is not null and 价格 is not null)
or
(客户号 is null and 地区 is null and 分类 is null and 价格 is not null)
or
(客户号 is not null and 地区 is not null and 分类 is not null and 价格 is not null)