求个pl/sql的例子
求个像这样的例子
表
name price num type
篮球 20 2 1
足球 10 3 1
羽毛球 5 10 1
白衣 28 1 2
红衣 32 3 2
黑衣 30 1 2
输出
type name totalprice
----- ----- ---------
1 篮球 40
-----------------------------
足球 30
-----------------------------
羽毛球 50
-----------------------------
total 120
2 白衣 28
-----------------------------
红衣 96
-----------------------------
黑衣 30
-----------------------------
total 154
怎么写，求语句
[解决办法]
- SQL code
with temp as (select '篮球' name,20 price,2 num,1 type from dualunion allselect '足球' name,10 price,3 num,1 type from dualunion allselect '羽毛球' name,5 price,10 num,1 type from dualunion allselect '白衣' name,28 price,1 num,2 type from dualunion allselect '红衣' name,32 price,3 num,2 type from dualunion allselect '黑衣' name,30 price,1 num,2 type from dual)select type,name,sum(price*num) totalprice from temp group by rollup(type,name) order by type
[解决办法]
这样可以么？
- SQL code
create table t1 (name varchar2(10), price number(10,2), num number(5), type varchar2(5));insert into t1 values ('篮球', 20,2,1);insert into t1 values ('足球', 10,3,1);insert into t1 values ('羽毛球', 5,10,1);insert into t1 values ('白衣', 28,1,2);insert into t1 values ('红衣', 32,3,2); insert into t1 values ('黑衣', 30,1,2);commit;select type,nvl(name,'合计') name,sum(price*num) t_pricefrom t1group by rollup(type,name) type name t_price-----------------------------------1 1 篮球 402 1 足球 303 1 羽毛球 504 1 合计 1205 2 白衣 286 2 黑衣 307 2 红衣 968 2 合计 1549 合计 274
[解决办法]
--name price num type
with t as (
select '篮球' as fname, 20 as price, 2 as num, 1 as ftype from dual
union all
select '足球', 10, 3, 1 from dual
union all
select '羽毛球', 5, 10, 1 from dual
union all
select '白衣', 28, 1, 2 from dual
union all
select '红衣', 32, 3, 2 from dual
union all
select '黑衣', 30, 1, 2 from dual
)
select ftype,nvl(fname,'total(' || case when grouping(ftype)=0 then to_char(ftype) else 'all' end || '):') as fname,sum(price) as totalprice from t
group by rollup(ftype,fname)
--结果
FTYPE FNAME TOTALPRICE
---------------------- ------------------------------------------------ ----------------------
1 篮球 20
1 足球 10
1 羽毛球 5
1 total(1): 35
2 白衣 28
2 红衣 32
2 黑衣 30
2 total(2): 90
total(all): 125
------解决方案--------------------
- SQL code
--写了两个，看你自己需要自己选吧，T代表你的表。[SYS@myoracle] SQL>WITH T AS( 2 SELECT '篮球' name,20 price,2 NUM, 1 TYPE FROM DUAL 3 UNION ALL 4 SELECT '足球' name,10 price,3 NUM, 1 TYPE FROM DUAL 5 UNION ALL 6 SELECT '羽毛球' name,5 price,10 NUM, 1 TYPE FROM DUAL 7 UNION ALL 8 SELECT '白衣' name,28 price,1 NUM, 2 TYPE FROM DUAL 9 UNION ALL 10 SELECT '红衣' name,32 price,3 NUM, 2 TYPE FROM DUAL 11 UNION 12 SELECT '黑衣' name,30 price,1 NUM, 2 TYPE FROM DUAL 13 )SELECT DECODE(NAME,NULL,'TOTAL',TYPE)TYPE,NAME,SUM(price*NUM) totalprice 14 FROM T 15 GROUP BY GROUPING SETS(TYPE,(TYPE,NAME));TYPE NAME TOTALPRICE---------------------------------------- ------ ----------1 篮球 401 足球 301 羽毛球 50TOTAL 1202 白衣 282 黑衣 302 红衣 96TOTAL 154已选择8行。[SYS@myoracle] SQL>WITH T AS 2 (SELECT '篮球' NAME, 20 PRICE, 2 NUM, 1 TYPE 3 FROM DUAL 4 UNION ALL 5 SELECT '足球' NAME, 10 PRICE, 3 NUM, 1 TYPE 6 FROM DUAL 7 UNION ALL 8 SELECT '羽毛球' NAME, 5 PRICE, 10 NUM, 1 TYPE 9 FROM DUAL 10 UNION ALL 11 SELECT '白衣' NAME, 28 PRICE, 1 NUM, 2 TYPE 12 FROM DUAL 13 UNION ALL 14 SELECT '红衣' NAME, 32 PRICE, 3 NUM, 2 TYPE 15 FROM DUAL 16 UNION 17 SELECT '黑衣' NAME, 30 PRICE, 1 NUM, 2 TYPE FROM DUAL) 18 SELECT DECODE(NAME, NULL, 'TOTAL', DECODE(ROW_, 1, TYPE, NULL)) TYPE, 19 NAME, 20 TOTALPRICE 21 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY NAME NULLS LAST) ROW_, 22 TYPE, 23 NAME, 24 TOTALPRICE 25 FROM (SELECT TYPE, NAME, SUM(PRICE * NUM) TOTALPRICE 26 FROM T 27 GROUP BY GROUPING SETS(TYPE,(TYPE, NAME))));TYPE NAME TOTALPRICE---------------------------------------- ------ ----------1 篮球 40 羽毛球 50 足球 30TOTAL 1202 白衣 28 黑衣 30 红衣 96TOTAL 154已选择8行。