总提示ORA-00979:不是GROUP BY 表达式，可以已经做分组了

嘉友云阁 2020-07-06 11:45:15

查询语句如下，
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;

...全文

1259 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

nayi_224 2020-07-08

打赏
举报

标量子查询直接进行聚合，这时group的语法会变得非常奇怪，不建议去研究这种冷门的东西。要么把标量子查询改写，要么外面再套一层。

wildwolv 2020-07-06

打赏
举报

wildwolv 2020-07-06

打赏
举报

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

riven2011 2020-07-06

打赏
举报

引用 5 楼 wildwolv 的回复:

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;

要改成外连接，不然不等价

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;

wildwolv 2020-07-06

打赏
举报

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;

嘉友云阁 2020-07-06