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

wjy217213 2010-10-29 03:56:21
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标识符无效
请大家帮忙看一下是什么原因,谢谢大家了

...全文
648 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
心中的彩虹 2010-10-29
  • 打赏
  • 举报
回复
[Quote=引用楼主 wjy217213 的回复:]
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=……
[/Quote]


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





YY_MM_DD 2010-10-29
  • 打赏
  • 举报
回复


-----使用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)
gelyon 2010-10-29
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 wjy217213 的回复:]
我的问题还有一个,我的这个表里需要小计和合计,合计固定最后一列,但是小计是任意组合的,我从界面任意组合选择需要小计的字段后,然后语句里就会根据出现的字段进行小计,所以上面的大家的建议我不知道怎么弄
[/Quote]
我6楼给你的就是例子!
wjy217213 2010-10-29
  • 打赏
  • 举报
回复
我的问题还有一个,我的这个表里需要小计和合计,合计固定最后一列,但是小计是任意组合的,我从界面任意组合选择需要小计的字段后,然后语句里就会根据出现的字段进行小计,所以上面的大家的建议我不知道怎么弄
gelyon 2010-10-29
  • 打赏
  • 举报
回复

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

westneverwin 2010-10-29
  • 打赏
  • 举报
回复
你出错的原因是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
westneverwin 2010-10-29
  • 打赏
  • 举报
回复

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
  • 打赏
  • 举报
回复

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
wjy217213 2010-10-29
  • 打赏
  • 举报
回复
没看懂,您的意思是我就不用union all 了,rollup ((a,b))
中的b又是从哪儿来的?
  • 打赏
  • 举报
回复
加合计不用单独加的嘛
--例如
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

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧