ORA-00979:不是GROUP BY表达式?

我已经找到了解决这个问题的方法,但是如果其中一个列是子查询,我如何将它包含在组中,或者我需要将它包含在组中。我会在这里粘贴查询。ORA-00979:不是GROUP BY表达式?

SELECT s.customerid, s.denomid, 

(SELECT su.quantity

FROM stockupdations su

WHERE s.customerid = su.custid

AND s.denomid = su.denomid

AND s.curid = su.curid) AS cur_stock, c.name AS cus_name, d.denomname AS denom,

cur.curcode AS currency

FROM stock s

LEFT JOIN customers c

ON s.customerid = c.custid

LEFT JOIN denomination d

ON d.denomid = s.denomid

LEFT JOIN currency cur

ON cur.curid = s.curid

GROUP BY s.denomid, s.customerid, c.name, d.denomname, cur.curcode

ORDER BY s.customerid ASC

回答:

WITH语句怎么样?

WITH tmp AS 

(

SELECT s.customerid, s.denomid,

c.name AS cus_name,

d.denomname AS denom,

cur.curcode AS currency

FROM stock s

LEFT JOIN customers c

ON s.customerid = c.custid

LEFT JOIN denomination d

ON d.denomid = s.denomid

LEFT JOIN currency cur

ON cur.curid = s.curid

GROUP BY s.denomid, s.customerid, c.name, d.denomname, cur.curcode

ORDER BY s.customerid ASC

)

SELECT tmp.customerid, tmp.denomid,

su.quantity,

tmp.cus_name,

tmp.denom,

tmp.currency

FROM tmp

INNER JOIN stockupdations su

ON tmp.customerid = su.custid

AND tmp.denomid = su.denomid

AND tmp.curid = su.curid

回答:

您可以在from子句中使用你的“内部查询”比对select

说我有一个客户表和订单表,

我能有这样的事情

SELECT C.CUSTOMER_ID, COUNT(T.ORDER_ID) 

FROM CUSTOMERS C

JOIN (SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, ORDER_STATUS FROM ORDERS O WHERE O.STATUS <> 'DECLINED') T

ON T.CUSTOMER_ID = C.CUSTOMER ID

GROUP BY C.CUSTOMER_ID

(这SQL仅仅是一个例子,我知道有更好的方法来写这篇文章,但我想不出任何其他例子)

回答:

你不必一次做所有事情。尝试将您的查询分解为多个部分。子查询,分析函数或其他复杂的逻辑对于外部查询看起来像简单的行。你为什么要使用`当你不使用任何聚合函数GROUP BY`(例如(不要担心性能,Oracle将重新写它,做任何事情都是一步到位,如果它是有道理的。)

--Step 3 

select [simple values]

from

(

--Step 2

select [insanity]

from

(

--Step 1

select [madness]

from

[impossible joins]

)

)

group by [simple values]

以上是 ORA-00979:不是GROUP BY表达式? 的全部内容, 来源链接: utcz.com/qa/264690.html

回到顶部