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