create table test
(
usName varchar2(10),
age number(18,2),
usmey number(18,2)
);
insert into test values ('aa',21,10);
insert into test values ('bb',22,10);
insert into test values ('cc',23,10);
insert into test values ('aa',21,10);
insert into test values ('bb',22,10);
insert into test values ('cc',23,10);
insert into test values ('dd',21,10);select usName,sum(usmey) from test group by rollup(usName);drop table test;结果
1 aa 20
2 bb 20
3 cc 20
4 dd 10
5 70我想总数结果里面去掉dd的值去掉,总数变为60,各位有何高招!
1 aa 20
2 bb 20
3 cc 20
4 dd 10
5 60
解决方案 »
- 不知道有没有免费的ORACLE?
- 求助:请研究下这个有没有办法整合成一条SQL语句
- !本地存储过程A中调用远程库的存储过程B,B过程中执行更新或插入语句,对自己库(远程的库)操作,COMMIT时发生异常:ORA-02064: 不支持
- pl sql 操作超大字符串
- 求救,Oracle 的数据库 更新一条语句的时候 不报错但更新不了
- 数据库导出导入问题,请大侠帮忙!
- oracle当中字体的修改
- oracel9i中文乱码问题?
- 刚刚学习Oracle的菜问题,请进!
- 求救:linux 上 oracle安装过程出错.请高手指点!
- 文件损坏了。。。。。。。。。
- 安装oracle10g出现对指定路径没有足够的写入权限。
SQL> select usName,sum(usmey) from test group by usname
2 union
3 select null,sum(usmey) from test where usname<>'dd'
4 ;
USNAME SUM(USMEY)
---------- ----------
aa 20
bb 20
cc 20
dd 10
60
SQL>
--如果你去掉dd是因为dd只有一条记录的话这样试试
SQL> select usName,sum(usmey) from test group by rollup(usName,usmey) having(count(usName))>1;USNAME SUM(USMEY)
---------- ----------
aa 20
aa 20
bb 20
bb 20
cc 20
cc 20
70
SQL> edi
已写入 file afiedt.buf 1 select usName,
2 decode(grouping(usName),1,sum(usmey)-(select usmey from test where usName='dd'),sum(usmey)) sum_usmey
3* from test group by rollup(usName)
SQL> /USNAME SUM_USMEY
---------- ----------
aa 20
bb 20
cc 20
dd 10
60SQL>
为什么不直接:
select usName,sum(usmey) from test where usName<>'dd' group by rollup(usName);啊!
2 union
3 select 'dd',sum(usmey) from test where usname='dd'
4 ;
USNAME SUM(USMEY)
---------- ----------
aa 20
bb 20
cc 20
dd 10
60
SQL> 如果usname上有索引的话可以试试这个
union
select 'dd',sum(usmey) from test where usname='dd'
;
这一句是什么意思啊 求大哥们给我解释一下!
GROUPING handles these problems. Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.GROUPING appears in the selection list portion of a SELECT statement. Its form is:SELECT … [GROUPING(dimension_column)…] …
GROUP BY … {CUBE | ROLLUP| GROUPING SETS} (dimension_column)
---
select usname,
case when grouping(usname)=1 then sum(usmey)-(select usmey from test where usname='dd')else sum(usmey) end sum_usmey
from test group by rollup (usname)