select nvl(姓名,'合计'),钱 from (select 姓名 ,sum(钱) 钱 from 表 group by rollup(姓名))
哈哈,我刚好看到 group by rollup 这个函数,还没来得及自己理解下呢,就看到你给我回复了,真是高手在民间啊.我先去试试.看看适不适合我这个SQL语句.谢谢俩位帮忙.
要的就是这个,不错.又学会了ORACLE 一个函数.哈哈!
WITH t AS ( SELECT '张三' AS fname, 1 AS money FROM dual UNION ALL SELECT '李四', 2 FROM dual UNION ALL SELECT '王五', 4 FROM dual UNION ALL SELECT '高帅', 9 FROM dual ) SELECT DECODE(dd,1,'总计:',fname) AS fname, money FROM (SELECT grouping(fname) AS dd, fname, SUM(money) AS money FROM t GROUP BY rollup(fname) ) --结果FNAME MONEY ----- ---------------------- 高帅 9 李四 2 王五 4 张三 1 总计: 16
如果只有一列求合计的话,我可以用个union 去把俩个查询结果拼到一起,但是我现在要查好多列..
(select 姓名 ,sum(钱) 钱 from 表 group by rollup(姓名))
( SELECT '张三' AS fname, 1 AS money FROM dual
UNION ALL
SELECT '李四', 2 FROM dual
UNION ALL
SELECT '王五', 4 FROM dual
UNION ALL
SELECT '高帅', 9 FROM dual
)
SELECT DECODE(dd,1,'总计:',fname) AS fname,
money
FROM
(SELECT grouping(fname) AS dd,
fname,
SUM(money) AS money
FROM t
GROUP BY rollup(fname)
)
--结果FNAME MONEY
----- ----------------------
高帅 9
李四 2
王五 4
张三 1
总计: 16