select distinct t1.lineid,'' as linesum,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3 where t1.detectid=t2.detectid and t1.detectid=t3.detectid
group by t1.lineid,t2.speedlevel
order by t1.lineid,t2.speedlevel
执行上面的语句没问题,但是我想加一个合计
select distinct t1.lineid,'' as linesum,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3 where t1.detectid=t2.detectid and t1.detectid=t3.detectid
union all
select distinct t1.lineid,'合计' as linesum,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3 where t1.detectid=t2.detectid and t1.detectid=t3.detectid
group by t1.lineid,t2.speedlevel
order by t1.lineid,t2.speedlevel
因些加上了上面红色的代码,但是报错误提示:
ORA-00904:t2.speedlevel标识符无效
请大家帮忙看一下是什么原因,谢谢大家了
group by t1.lineid,t2.speedlevel
order by t1.lineid,t2.speedlevel
执行上面的语句没问题,但是我想加一个合计
select distinct t1.lineid,'' as linesum,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3 where t1.detectid=t2.detectid and t1.detectid=t3.detectid
union all
select distinct t1.lineid,'合计' as linesum,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3 where t1.detectid=t2.detectid and t1.detectid=t3.detectid
group by t1.lineid,t2.speedlevel
order by t1.lineid,t2.speedlevel
因些加上了上面红色的代码,但是报错误提示:
ORA-00904:t2.speedlevel标识符无效
请大家帮忙看一下是什么原因,谢谢大家了
解决方案 »
- 这段PL/SQL为何报错?
- 招Oracle DBA,内部推荐
- 当我把数据导入到D盘后 我怎么做 才能使用这个数据库?
- oracle的clob对象查询起来好慢啊,有什么方法可以解决吗?
- oracle spatial与jdbc的问题
- 怎样在sql*plus里写SQL对数据库做备份和恢复? 我要实现的是用程序代码控制oracle的备份和恢复请指点一二??
- 在oracle里for update of 是
- 导入数据库的中文都变成了???,应该是字符集的问题,请问如何更改字符集?
- 求救:oracle9i在win2k上setup.exe没反应
- 关于case语句
- 增量为1的备份:level 1 是什么意思?
- 如何让PL/SQL连接到本地的ORACLE数据库
--例如
SQL> with a as (
2 select 'a' A, 'b' B ,5 C ,2 D from dual
3 union all
4 select 'w', 'b', 1, 3 from dual
5 union all
6 select 'x' ,'x' ,3 ,1 from dual
7 )
8 select decode(grouping(A),1,'合計',A) A
9 ,B,sum(C) C,SUM(D) D
10 from a
11 group by rollup ((a,b))
12 /A B C D
---- - ---------- ----------
a b 5 2
w b 1 3
x x 3 1
合計 9 6
中的b又是从哪儿来的?
select decode(grouping(t1.lineid),1,'合計',t1.lineid) lineid,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3
where t1.detectid=t2.detectid
and t1.detectid=t3.detectid
group by rollup ((t1.lineid,t2.speedlevel))
order by lineid,t2.speedlevel
select distinct t1.lineid,decode(grouping(t2.speedlevel),1,'合计') as linesum,
t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3 where t1.detectid=t2.detectid and t1.detectid=t3.detectid
group by t1.lineid,rollup(t2.speedlevel)
order by t1.lineid,2,t2.speedlevel
UNION 之后的ORDER BY 比较烦
比如
select 1 as aa from dual a
order by a.dummy这样不出错,但是UNION之后
select 1 as dummy from dual a
union all
select 2 from dual a
order by a.dummy
这样就不行了
你可以直接改成
order by 1,3
--
SELECT CASE
WHEN deptno IS NULL THEN '合计'
WHEN deptno IS NOT NULL AND empno IS NULL THEN '小计'
ELSE ''||deptno
END deptno,
empno,
ename,
SUM(sal) total_sal
FROM emp
GROUP BY GROUPING SETS((deptno),(deptno, empno, ename),());DEPTNO EMPNO ENAME TOTAL_SAL
10 7782 CLARK 1450
10 7839 KING 5000
10 7934 MILLER 1300
小计 7750
20 7369 SMITH 800
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7876 ADAMS 1100
20 7902 FORD 3000
小计 10875
30 7900 JAMES 950
30 7499 ALLEN 1600
30 7521 WARD 1250
30 7654 MARTIN 1250
30 7698 BLAKE 2850
30 7844 TURNER 1500
30 7955 BLACK 1250
30 7999 MAXTON 11250
小计 21900
合计 40525
-----而是来自union all之后的表,所以你必须这样写,就必须加上我加的select * from
select distinct t1.lineid,
'' as linesum,
t2.speedlevel,
sum(decode(t2.exceptionitem, 0, t2.level4count, 0)) as a
from pw_run_record t1,
pw_detect_exceptionstatic t2,
pw_detect_tqistatic t3
where t1.detectid = t2.detectid
and t1.detectid = t3.detectid
union all
select *
from (select distinct t1.lineid,
'合计' as linesum,
t2.speedlevel,
sum(decode(t2.exceptionitem, 0, t2.level4count, 0)) as a
from pw_run_record t1,
pw_detect_exceptionstatic t2,
pw_detect_tqistatic t3
where t1.detectid = t2.detectid
and t1.detectid = t3.detectid
group by t1.lineid, t2.speedlevel
order by t1.lineid, t2.speedlevel)
select distinct t1.lineid,'' as linesum,t2.speedlevel,
decode(t2.exceptionitem,0,t2.level4count,0) as a
from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3
where t1.detectid=t2.detectid and t1.detectid=t3.detectid
union all
select distinct t1.lineid,'合计' as linesum,t2.speedlevel,sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a
from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3
where t1.detectid=t2.detectid and t1.detectid=t3.detectid
group by t1.lineid,t2.speedlevel
order by lineid,speedlevel
--试试粒度统计select t1.lineid,
decode(grouping(t1.lineid)+grouping(t2.speedlevel),2,'总计',1,'小计',0,t2.speedlevel)
sum(decode(t2.exceptionitem,0,t2.level4count,0)) as a
from pw_run_record t1,pw_detect_exceptionstatic t2,pw_detect_tqistatic t3
where t1.detectid=t2.detectid and t1.detectid=t3.detectid
group by rollup(t1.lineid,t2.speedlevel)
order by t1.lineid,t2.speedlevel