oracle SQL 组函数嵌套太深
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16576338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL group function nested too deeply
提问by user1393064
I'm trying to create an sql query that will return the smallest occurrence of an id appearing between two tables however I keep getting the error with the line HAVING MIN(COUNT(E.C_SE_ID)). Oracle is saying that the group by function is nested too deeply.
I cannot think of another way of returning C_SE_ID
我正在尝试创建一个 sql 查询，该查询将返回出现在两个表之间的 id 的最小出现次数，但是我一直收到该行的错误HAVING MIN(COUNT(E.C_SE_ID))。Oracle 说 group by 函数嵌套太深。我想不出另一种回归方式 C_SE_ID
SELECT CS.C_SE_ID, MIN(COUNT(E.C_SE_ID))
FROM COURSE_SECTION CS, ENROLLMENT E, LOCATION L
WHERE CS.C_SE_ID=E.C_SE_ID
AND CS.LOC_ID=L.LOC_ID
AND L.BLDG_CODE='DBW'
GROUP BY CS.C_SE_ID
HAVING MIN(COUNT(E.C_SE_ID));
in enrollment table s_idand c_se_idare linked, I'm trying to get all the s_idthat are related to that c_se_id. with the updated query oracle doesn't like the select *(for obvious reasons) but when I change it too e.c_Se_idI get nothing.
在注册表中s_id并c_se_id已链接，我正在尝试获取与s_id此相关的所有内容c_se_id。使用更新的查询 oracle 不喜欢select *（出于明显的原因），但是当我也更改它时，我什么也e.c_Se_id得不到。
SELECT E.S_ID
FROM COURSE_SECTION CS, ENROLLMENT E
WHERE CS.C_SE_ID=E.C_SE_ID
AND E.C_SE_ID =(
select *
from (select CS.C_SE_ID, count(*) as cnt,
max(count(*)) over (partition by cs.c_se_id) as maxcnt
from COURSE_SECTION CS join
ENROLLMENT E
on CS.C_SE_ID=E.C_SE_ID join
LOCATION L
on CS.LOC_ID=L.LOC_ID
where L.BLDG_CODE='DBW'
GROUP BY CS.C_SE_ID
order by count(*) desc
) t
where cnt = maxcnt);
回答by Gordon Linoff
One way to do this is by nesting your query and then choosing the first row in the output:
一种方法是嵌套查询，然后选择输出中的第一行：
select C_SE_ID, cnt
from (select CS.C_SE_ID, count(*) as cnt
from COURSE_SECTION CS join
ENROLLMENT E
on CS.C_SE_ID=E.C_SE_ID join
LOCATION L
on CS.LOC_ID=L.LOC_ID
where L.BLDG_CODE='DBW'
GROUP BY CS.C_SE_ID
order by count(*) desc
) t
where rownum = 1
Note I updated the join syntax to the more modern version using oninstead of where.
注意我使用on而不是将连接语法更新为更现代的版本where。
If you want allminimum values (and there are more than one), then I would use analytic functions. It is a very similar idea to your original query:
如果您想要所有最小值（并且不止一个），那么我将使用解析函数。这与您的原始查询非常相似：
select *
from (select CS.C_SE_ID, count(*) as cnt,
max(count(*)) over (partition by cs.c_se_id) as maxcnt
from COURSE_SECTION CS join
ENROLLMENT E
on CS.C_SE_ID=E.C_SE_ID join
LOCATION L
on CS.LOC_ID=L.LOC_ID
where L.BLDG_CODE='DBW'
GROUP BY CS.C_SE_ID
order by count(*) desc
) t
where cnt = maxcnt;
Try this instead of your original query:
试试这个而不是你的原始查询：
SELECT E.S_ID
FROM ENROLLMENT E
where E.C_SE_ID in (select C_SE_ID
from (select CS.C_SE_ID, count(*) as cnt,
max(count(*)) over (partition by cs.c_se_id) as maxcnt
from ENROLLMENT E
LOCATION L
on CS.LOC_ID=L.LOC_ID
where L.BLDG_CODE='DBW'
GROUP BY e.C_SE_ID
) t
where cnt = maxcnt)
);
In addition to fixing the joins, I also removed all references to course_section. This table doesn't seem to be used (unless for filtering results), and removing it implifies the queries.
除了修复连接之外，我还删除了对course_section. 这个表似乎没有被使用（除非用于过滤结果），删除它意味着查询。
