現在在做報表 需要給出的格式id number name
1 10 i
2 20 i
3 30 i
'' 60 '小記'
4 10 j
5 20 j
6 30 j
'' 60 '小記'
'' 120 '合計'現在做的方式是
select * from AA
union all
select '', sum(number),'小記' from AA group by name
union all
select '',sum(number),'合計' from AA由於AA是一個複雜的查詢 用我這種方式寫的需要重複寫3次 這樣會造成需要些大量的代碼
有什麽其他的寫法 讓代碼可以簡短些
1 10 i
2 20 i
3 30 i
'' 60 '小記'
4 10 j
5 20 j
6 30 j
'' 60 '小記'
'' 120 '合計'現在做的方式是
select * from AA
union all
select '', sum(number),'小記' from AA group by name
union all
select '',sum(number),'合計' from AA由於AA是一個複雜的查詢 用我這種方式寫的需要重複寫3次 這樣會造成需要些大量的代碼
有什麽其他的寫法 讓代碼可以簡短些
UNION ALL
select '' ID,SUM(NUMBER),NAME from aa
GROUP BY CUBE(NAME)
With t As (
select 1 Id,10 numb, 'i' Name From dual
Union All
select 2 Id,20 numb, 'i' Name From dual
Union All
select 3 Id,30 numb, 'i' Name From dual
Union All
select 4 Id,10 numb, 'j' Name From dual
Union All
select 5 Id,20 numb, 'j' Name From dual
Union All
select 6 Id,30 numb, 'j' Name From dual
)
Select id, Sum(numb), (case when id Is null and name Is Not null then '小記'
When id is Null and Name Is Null Then '合計' else Name End) Name From t
Group By Rollup(Name,Id)
select 1 Id,10 numb, 'i' Name From dual Union All
select 2 Id,20 numb, 'i' Name From dual Union All
select 3 Id,30 numb, 'i' Name From dual Union All
select 4 Id,10 numb, 'j' Name From dual Union All
select 5 Id,20 numb, 'j' Name From dual Union All
select 6 Id,30 numb, 'j' Name From dual
)
SELECT id, SUM(numb) numb, decode(grouping_id(id, NAME), 3, '合计', 2, '小記', NAME) NAME
FROM t
GROUP BY ROLLUP(NAME, Id)
用rollup
不過現在還有一個問題
name content number
a a1 1
a a2 2
a a3 3
b b1 1
b b2 2
b b3 3
如果我想要在查詢的結果上也有 content的字段 要如何處理