如下数据:
GROUP_ID JOB NAME SALARY
---------- ---------- ---------- ----------
10 Coding Bruce 1000
10 Programmer Clair 1000
10 Architect Gideon 1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin 2000
20 Director Michael 2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard 3000
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000分组统计:
select group_id,sum(salary) from group_test group by rollup(group_id); GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000翻译一下上面的SQL语句如下(union all一个统计所有数据的行):select group_id,sum(salary) from group_test group by group_id
union all
select null, sum(salary) from group_test order by 1;展示结果相同问题如下:
上面出现了一个select null.....
这样,在oracle中运行完全正确,可是,当应用到项目中时,报错:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];请问这是null引起的吗?若是,有什么解决之道?求各位朋友帮忙!!
GROUP_ID JOB NAME SALARY
---------- ---------- ---------- ----------
10 Coding Bruce 1000
10 Programmer Clair 1000
10 Architect Gideon 1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin 2000
20 Director Michael 2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard 3000
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000分组统计:
select group_id,sum(salary) from group_test group by rollup(group_id); GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000翻译一下上面的SQL语句如下(union all一个统计所有数据的行):select group_id,sum(salary) from group_test group by group_id
union all
select null, sum(salary) from group_test order by 1;展示结果相同问题如下:
上面出现了一个select null.....
这样,在oracle中运行完全正确,可是,当应用到项目中时,报错:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];请问这是null引起的吗?若是,有什么解决之道?求各位朋友帮忙!!
select nvl(group_id,'合计') as group_id, sum(nvl(salary,0)) as salary
from group_test group by rollup(group_id);-- 如果你的 group_id 不是字符串类型的话:
select nvl(to_char(group_id),'合计') as group_id, sum(nvl(salary,0)) as salary
from group_test group by rollup(group_id);
正解!nvl()是专门用来处理字符串空值问题的。