请教高手union all与group by一起用时出错的一个问题

Question

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标识符无效
请大家帮忙看一下是什么原因，谢谢大家了

Answer 1

加合计不用单独加的嘛
--例如
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

Answer 2

没看懂，您的意思是我就不用union all 了，rollup ((a,b))
中的b又是从哪儿来的？

Answer 3

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

Answer 4

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

Answer 5

你出错的原因是ORDER BY 
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

Answer 6

--
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

Answer 7

-----使用union all 是先组合,然后再排序列,所以你使用的 order by t1.lineid, t2.speedlevel并不是来自t1,和t2表
-----而是来自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)

Answer 8

--你的报错修改，下面的你运行就不会报错了
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