解决方案 »
- 求统计某个月所有天的统计信息
- 请问北京除了原厂,哪里培训oracle DBA比较好?
- 登陆广域网中的oracle数据库,怎样才能不让管理员查出我的IP地址?
- http://serach:5500/em 出错
- 复杂索引中出现多个字段,对多个字段的不同使用师父出现不同的效率,请问怎么确定这个相应的效率问题?
- 求Linux下 Oracle9.2.0.4的建库方法和语句~~~
- 用角色用户在asp.net中执行where中有用户定义的函数的sql语句,会报错。急急急
- PL/SQL中的小问题。
- oracle表索引失效,会影响其他表的查询么?
- sql 日期连贯
- 请教一句SQL查询统计的问题,请大家帮忙一下
- oracle备份
select '篮球' name,20 price,2 num,1 type from dual
union all
select '足球' name,10 price,3 num,1 type from dual
union all
select '羽毛球' name,5 price,10 num,1 type from dual
union all
select '白衣' name,28 price,1 num,2 type from dual
union all
select '红衣' name,32 price,3 num,2 type from dual
union all
select '黑衣' 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
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_price
from t1
group by rollup(type,name) type name t_price
-----------------------------------
1 1 篮球 40
2 1 足球 30
3 1 羽毛球 50
4 1 合计 120
5 2 白衣 28
6 2 黑衣 30
7 2 红衣 96
8 2 合计 154
9 合计 274
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
--写了两个,看你自己需要自己选吧,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 篮球 40
1 足球 30
1 羽毛球 50
TOTAL 120
2 白衣 28
2 黑衣 30
2 红衣 96
TOTAL 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
足球 30
TOTAL 120
2 白衣 28
黑衣 30
红衣 96
TOTAL 154已选择8行。