查詢陳述句如下，
SELECT B.SPCODE SPCODE,
B.BARCODE BARCODE,
B.NAME NAME,
C.FDBH FDBH,
sum(C.XSJE) XSJE,sum(C.XSSL) XSSL,
(SELECT SUM(P.JHSL*P.JHDJ)/SUM(P.JHSL) from SPFPCJC P,KCPCXX K WHERE P.KCPCH=K.KCPCH AND P.SP_ID=C.SP_ID AND TRUNC(K.JHSJ)>=TO_DATE('2020.01.01') AND TRUNC(K.JHSJ)<=TO_DATE('2020.01.31')) JHCB
FROM SKTXSJL L,
SKTXSJLC C,
SKTXSJLM M,
SPXX_JB B
WHERE ( L.SKTNO = C.SKTNO ) and
( L.SKTNO = M.SKTNO ) and
( C.SP_ID = B.SP_ID ) and
( L.JLBH = C.JLBH ) and
( L.JLBH = M.JLBH ) and
( ( M.SKFS = 29 ) AND
( trunc(L.JYSJ) >= '2020.01.01' AND TRUNC(L.JYSJ)<='2020.01.31') )
GROUP BY B.SPCODE,
B.BARCODE,
B.NAME,
C.FDBH;
uj5u.com熱心網友回復：
SELECT B.SPCODE SPCODE,B.BARCODE BARCODE,
B.NAME NAME,
C.FDBH FDBH,
sum(C.XSJE) XSJE,sum(C.XSSL) XSSL,
A.JHCB
FROM SKTXSJL L,
SKTXSJLC C,
SKTXSJLM M,
SPXX_JB B，
(SELECT SUM(P.JHSL*P.JHDJ)/SUM(P.JHSL) as JHCB from SPFPCJC P,KCPCXX K WHERE P.KCPCH=K.KCPCH AND P.SP_ID=C.SP_ID AND TRUNC(K.JHSJ)>=TO_DATE('2020.01.01') AND TRUNC(K.JHSJ)<=TO_DATE('2020.01.31')) A
WHERE ( L.SKTNO = C.SKTNO ) and
( L.SKTNO = M.SKTNO ) and
( C.SP_ID = B.SP_ID ) and
( L.JLBH = C.JLBH ) and
( L.JLBH = M.JLBH ) and
( ( M.SKFS = 29 ) AND
( trunc(L.JYSJ) >= '2020.01.01' AND TRUNC(L.JYSJ)<='2020.01.31') )
GROUP BY B.SPCODE,
B.BARCODE,
B.NAME,
C.FDBH,
A.JHCB;
還有你這種trunc(L.JYSJ)的寫法不知道有沒有問題，我一般都用to_.SPCODE SPCODE,
B.BARCODE BARCODE,
B.NAME NAME,
C.FDBH FDBH,
sum(C.XSJE) XSJE,sum(C.XSSL) XSSL,
A.JHCB
FROM SKTXSJL L,
SKTXSJLC C,
SKTXSJLM M,
SPXX_JB B，
(SELECT SUM(P.JHSL*P.JHDJ)/SUM(P.JHSL) as JHCB from SPFPCJC P,KCPCXX K WHERE P.KCPCH=K.KCPCH AND P.SP_ID=C.SP_ID AND TRUNC(K.JHSJ)>=TO_DATE('2020.01.01') AND TRUNC(K.JHSJ)<=TO_DATE('2020.01.31')) A
WHERE ( L.SKTNO = C.SKTNO ) and
( L.SKTNO = M.SKTNO ) and
( C.SP_ID = B.SP_ID ) and
( L.JLBH = C.JLBH ) and
( L.JLBH = M.JLBH ) and
( ( M.SKFS = 29 ) AND
( trunc(L.JYSJ) >= '2020.01.01' AND TRUNC(L.JYSJ)<='2020.01.31') )
GROUP BY B.SPCODE,
B.BARCODE,
B.NAME,
C.FDBH,
A.JHCB;
還有你這種trunc(L.JYSJ)的寫法不知道有沒有問題，我一般都用to_char
uj5u.com熱心網友回復：
SELECT B.SPCODE SPCODE,B.BARCODE BARCODE,
B.NAME NAME,
C.FDBH FDBH,
sum(C.XSJE) XSJE,sum(C.XSSL) XSSL,
A.JHCB
FROM SKTXSJL L,
SKTXSJLC C,
SKTXSJLM M,
SPXX_JB B，
(SELECT SUM(P.JHSL*P.JHDJ)/SUM(P.JHSL) as JHCB from SPFPCJC P,KCPCXX K WHERE P.KCPCH=K.KCPCH AND P.SP_ID=C.SP_ID AND TRUNC(K.JHSJ)>=TO_DATE('2020.01.01') AND TRUNC(K.JHSJ)<=TO_DATE('2020.01.31')) A
WHERE ( L.SKTNO = C.SKTNO ) and
( L.SKTNO = M.SKTNO ) and
( C.SP_ID = B.SP_ID ) and
( L.JLBH = C.JLBH ) and
( L.JLBH = M.JLBH ) and
( ( M.SKFS = 29 ) AND
( trunc(L.JYSJ) >= '2020.01.01' AND TRUNC(L.JYSJ)<='2020.01.31') )
GROUP BY B.SPCODE,
B.BARCODE,
B.NAME,
C.FDBH,
A.JHCB;
uj5u.com熱心網友回復：
提示C.FDBH識別符號無效uj5u.com熱心網友回復：
錯了，是c.sp_id識別符號無效uj5u.com熱心網友回復：
SELECT B.SPCODE SPCODE,B.BARCODE BARCODE,
B.NAME NAME,
C.FDBH FDBH,
sum(C.XSJE) XSJE,sum(C.XSSL) XSSL,
SUM(P.JHSL*P.JHDJ)/SUM(P.JHSL) as JHCB
FROM SKTXSJL L,
SKTXSJLC C,
SKTXSJLM M,
SPXX_JB B，
SPFPCJC P,
KCPCXX K
WHERE ( L.SKTNO = C.SKTNO ) and
( L.SKTNO = M.SKTNO ) and
( C.SP_ID = B.SP_ID ) and
( L.JLBH = C.JLBH ) and
( L.JLBH = M.JLBH ) and
( ( M.SKFS = 29 ) AND
( trunc(L.JYSJ) >= '2020.01.01' AND TRUNC(L.JYSJ)<='2020.01.31') )
and P.KCPCH=K.KCPCH
AND P.SP_ID=C.SP_ID
AND TRUNC(K.JHSJ)>=TO_DATE('2020.01.01')
AND TRUNC(K.JHSJ)<=TO_DATE('2020.01.31')
GROUP BY B.SPCODE,
B.BARCODE,
B.NAME,
C.FDBH;
uj5u.com熱心網友回復：
要改成外連接，不然不等價
SELECT B.SPCODE SPCODE
,B.BARCODE BARCODE
,B.NAME NAME
,C.FDBH FDBH
,SUM(C.XSJE) XSJE
,SUM(C.XSSL) XSSL
,SUM(T.JHCB) JHCB
FROM SKTXSJL L
,SKTXSJLC C
,SKTXSJLM M
,SPXX_JB B
,(SELECT SUM(P.JHSL * P.JHDJ) / SUM(P.JHSL) JHCB
,P.SP_ID
FROM SPFPCJC P
,KCPCXX K
WHERE P.KCPCH = K.KCPCH
AND TRUNC(K.JHSJ) >= TO_DATE('2020.01.01')
AND TRUNC(K.JHSJ) <= TO_DATE('2020.01.31')
GROUP BY P.SP_ID) T
WHERE (L.SKTNO = C.SKTNO)
AND (L.SKTNO = M.SKTNO)
AND (C.SP_ID = B.SP_ID)
AND (L.JLBH = C.JLBH)
AND (L.JLBH = M.JLBH)
AND
((M.SKFS = 29) AND (trunc(L.JYSJ) >= '2020.01.01' AND TRUNC(L.JYSJ) <= '2020.01.31'))
AND (C.SP_ID = T.SP_ID(+))
GROUP BY B.SPCODE
,B.BARCODE
,B.NAME
,C.FDBH;
uj5u.com熱心網友回復：
標量子查詢直接進行聚合，這時group的語法會變得非常奇怪，不建議去研究這種冷門的東西。要么把標量子查詢改寫，要么外面再套一層。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8461.html
標籤：基礎和管理