Oracle throws Not a group by expression
我有一个查询，我使用一个右连接日期表的表，这意味着我得到所有日期值。我只想让我的表格结果集的边界之间的值。我有以下查询
|
1
2 3 4 5 6 |
SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR, COUNT(DISTINCT FPP.ID) AS Total
FROM FACT_PAY_PAYMENT FPP RIGHT JOIN DIM_DATE DD ON FPP.REQUESTED_EXECUTION_DATE_ID = DD.ID GROUP BY DD.CAL_YEAR, DD.WEEK_OF_YEAR HAVING DD.ID >= MIN(FPP.REQUESTED_EXECUTION_DATE_ID) AND DD.ID <= MAX(FPP.REQUESTED_EXECUTION_DATE_ID); |
但是当我执行此操作时，我从 Oracle
收到以下错误
Error: ORA-00979: not a GROUP BY expression
SQLState: 42000 ErrorCode: 979
我的查询有什么问题？
在您的查询中，问题是最后一条语句 (having):
|
1
2 3 4 5 |
SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR, COUNT(DISTINCT FPP.ID) AS Total
FROM FACT_PAY_PAYMENT FPP RIGHT JOIN DIM_DATE DD ON FPP.REQUESTED_EXECUTION_DATE_ID = DD.ID GROUP BY DD.CAL_YEAR,DD.WEEK_OF_YEAR —> Having DD.ID >= MIN(FPP.REQUESTED_EXECUTION_DATE_ID) AND DD.ID <= MAX(FPP.REQUESTED_EXECUTION_DATE_ID) |
确实，have 也应该应用于 group by 中包含的字段，而 DD.ID 则不是这样。
所以甲骨文抱怨(现在更清楚)消息”(DD.ID是)not(in)a group by expression”
如果与您的业务逻辑兼容，可能的解决方案是将您的 having 子句转换为简单的 where 子句：
|
1
2 3 4 5 6 |
SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR, COUNT(DISTINCT FPP.ID) AS Total
FROM FACT_PAY_PAYMENT FPP RIGHT JOIN DIM_DATE DD ON FPP.REQUESTED_EXECUTION_DATE_ID = DD.ID WHERE DD.ID >= … AND DD.ID <= … GROUP BY DD.CAL_YEAR,DD.WEEK_OF_YEAR |
在这里你可以找到对 having vs where 子句的很好的解释。
您似乎想要最小值和最大值之间的所有日期值，即使是那些没有匹配值的日期值。我猜每年和每周的聚合只会从 DD 获得少数 id。所以，一种方法是：
|
1
2 3 4 5 6 7 |
SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR, COUNT(DISTINCT FPP.ID) AS Total
FROM DIM_DATE DD LEFT JOIN FACT_PAY_PAYMENT FPP ON FPP.REQUESTED_EXECUTION_DATE_ID = DD.ID GROUP BY DD.CAL_YEAR, DD.WEEK_OF_YEAR HAVING MAX(DD.ID) >= MIN(FPP.REQUESTED_EXECUTION_DATE_ID) AND MIN(DD.ID) <= MAX(FPP.REQUESTED_EXECUTION_DATE_ID); |
但是，在 having 子句中执行此过滤是浪费精力——查询已经聚合了所有内容。这是一种在 where:
中获取它的方法
|
1
2 3 4 5 6 7 8 9 10 |
SELECT DD.CAL_YEAR, DD.WEEK_OF_YEAR, COUNT(DISTINCT FPP.ID) AS Total
FROM DIM_DATE DD LEFT JOIN FACT_PAY_PAYMENT FPP ON FPP.REQUESTED_EXECUTION_DATE_ID = DD.ID CROSS JOIN (SELECT MIN(FPP.REQUESTED_EXECUTION_DATE_ID) AS minredi, MAX(FPP.REQUESTED_EXECUTION_DATE_ID) AS maxredi FROM FACT_PAY_PAYMENT ) minmax WHERE DD.ID >= minmax.minredi AND DD.ID <= minmax.maxredi GROUP BY DD.CAL_YEAR, DD.WEEK_OF_YEAR; |
来源：https://www.codenong.com/33126951/
