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