SELECT a.prod_cname, COUNT(a.box_num) box_num_no, SUM(a.mat_gross_wt) mat_gross_wt, SUM(a.mat_wt) mat_wt, (SELECT code_desc_1_content FROM tep0002 b WHERE a.ord_attr2 = b.code AND b.code_class = 'QMJ2') zhijin FROM (SELECT b.ord_attr2, b.ord_attr3, b.ord_attr5, a.stock_no, a.prod_code, a.prod_cname, a.box_num, a.company_code, SUM(a.mat_wt) mat_wt, SUM(a.mat_gross_wt) mat_gross_wt FROM tmm0001 a, tsosc01 b WHERE a.stock_no = 'B01' AND a.company_code = 'JSJM' AND a.prod_code = 'J1' AND a.psc = b.psc AND b.company_code = 'JSJM' GROUP BY b.ord_attr2, b.ord_attr3, b.ord_attr5, stock_no, a.prod_code, a.prod_cname, a.box_num, a.company_code) a WHERE a.company_code = 'JSJM' AND a.stock_no = 'B01' AND a.prod_code IN ('J1') GROUP BY a.prod_cname, a.ord_attr2 ORDER BY a.prod_cname, a.ord_attr2
select prod_cname, zhijin, count(A.BOX_NUM) BOX_NUM_NO, SUM(A.MAT_GROSS_WT) MAT_GROSS_WT, SUM(A.MAT_WT) MAT_WT from (SELECT A.PROD_CNAME, (select code_desc_1_content from tep0002 b where A.ORD_ATTR2 = b.code and b.CODE_CLASS = 'QMJ2') ZHIJIN FROM (SELECT b.ORD_ATTR2, b.ORD_ATTR3, b.ORD_ATTR5, a.STOCK_NO, a.prod_code, a.PROD_CNAME, a.BOX_NUM, a.COMPANY_CODE, SUM(a.MAT_WT) MAT_WT, SUM(a.MAT_GROSS_WT) MAT_GROSS_WT FROM TMM0001 a, TSOSC01 b where a.STOCK_NO = 'B01' and a.COMPANY_CODE = 'JSJM' and a.prod_code = 'J1' and a.psc = b.psc and b.COMPANY_CODE = 'JSJM' GROUP BY b.ORD_ATTR2, b.ORD_ATTR3, b.ORD_ATTR5, STOCK_NO, a.prod_code, a.PROD_CNAME, a.BOX_NUM, a.COMPANY_CODE) A WHERE A.COMPANY_CODE = 'JSJM' AND A.STOCK_NO = 'B01' AND A.PROD_CODE IN ('J1')) group by PROD_CNAME, zhijin order by PROD_CNAME
[size=13px]感谢1楼的高手,能否告诉我为何group by 字句 一定要用 a.ord_attr2 ,不能用别名 zhijin呢?
在同一层次中,SQL并不能识别别名。 如下:select c1 bieming from (select 13 c1 from dual) where bieming > 10; --报错select c1 bieming from (select 13 c1 from dual) where c1> 10;--正常执行
(SELECT code_desc_1_content
FROM tep0002 b
WHERE a.ord_attr2 = b.code
AND b.code_class = 'QMJ2') zhijin
FROM (SELECT b.ord_attr2, b.ord_attr3, b.ord_attr5, a.stock_no, a.prod_code, a.prod_cname, a.box_num, a.company_code,
SUM(a.mat_wt) mat_wt, SUM(a.mat_gross_wt) mat_gross_wt
FROM tmm0001 a, tsosc01 b
WHERE a.stock_no = 'B01'
AND a.company_code = 'JSJM'
AND a.prod_code = 'J1'
AND a.psc = b.psc
AND b.company_code = 'JSJM'
GROUP BY b.ord_attr2, b.ord_attr3, b.ord_attr5, stock_no, a.prod_code, a.prod_cname, a.box_num, a.company_code) a
WHERE a.company_code = 'JSJM'
AND a.stock_no = 'B01'
AND a.prod_code IN ('J1')
GROUP BY a.prod_cname, a.ord_attr2
ORDER BY a.prod_cname, a.ord_attr2
zhijin,
count(A.BOX_NUM) BOX_NUM_NO,
SUM(A.MAT_GROSS_WT) MAT_GROSS_WT,
SUM(A.MAT_WT) MAT_WT
from (SELECT A.PROD_CNAME,
(select code_desc_1_content
from tep0002 b
where A.ORD_ATTR2 = b.code
and b.CODE_CLASS = 'QMJ2') ZHIJIN
FROM (SELECT b.ORD_ATTR2,
b.ORD_ATTR3,
b.ORD_ATTR5,
a.STOCK_NO,
a.prod_code,
a.PROD_CNAME,
a.BOX_NUM,
a.COMPANY_CODE,
SUM(a.MAT_WT) MAT_WT,
SUM(a.MAT_GROSS_WT) MAT_GROSS_WT
FROM TMM0001 a, TSOSC01 b
where a.STOCK_NO = 'B01'
and a.COMPANY_CODE = 'JSJM'
and a.prod_code = 'J1'
and a.psc = b.psc
and b.COMPANY_CODE = 'JSJM'
GROUP BY b.ORD_ATTR2,
b.ORD_ATTR3,
b.ORD_ATTR5,
STOCK_NO,
a.prod_code,
a.PROD_CNAME,
a.BOX_NUM,
a.COMPANY_CODE) A
WHERE A.COMPANY_CODE = 'JSJM'
AND A.STOCK_NO = 'B01'
AND A.PROD_CODE IN ('J1'))
group by PROD_CNAME, zhijin
order by PROD_CNAME
如下:select c1 bieming from (select 13 c1 from dual) where bieming > 10; --报错select c1 bieming from (select 13 c1 from dual) where c1> 10;--正常执行
查询语句的执行顺序:
from、where、group by、rollup、having、select后的字段、order by