SQL组函数嵌套太深 [英] SQL group function nested too deeply

问题描述

我试图创建一个sql查询，它将返回出现在两个表之间的最小的id，但是我一直用 HAVING MIN（COUNT（E.C_SE_ID））行来获取错误。 。甲骨文表示，按功能分组嵌套得太深了。
我想不出另一种返回方式 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））; 
  

注册表 s_id 和 c_se_id 是链接的，我试图获取与 c_se_id 相关的所有 s_id code>。更新的查询oracle不喜欢 select * （出于显而易见的原因），但是当我更改它时 e.c_Se_id

  SELECT E.S_ID 
从COURSE_SECTION CS，ENROLLMENT E 
 WHERE CS.C_SE_ID = E.C_SE_ID 
 AND E.C_SE_ID =（
 select * 
 from（选择CS.C_SE_ID，count（*）as cnt，
 max（count（*））over .c_se_id）作为maxcnt 
来自COURSE_SECTION CS连接
 ENCOLLMENT E 
对CS.C_SE_ID = E.C_SE_ID连接
位置L 
对CS.LOC_ID = L.LOC_ID 
其中L.BLDG_CODE ='DBW'
 GROUP BY CS.C_SE_ID 
按次数排序（*）desc 
）t 
 where cnt = maxcnt）; 
  




解决方案

然后选择输出中的第一行：



 选择C_SE_ID，cnt 
 from（选择CS.C_SE_ID，count（ *）作为cnt 
来自COURSE_SECTION CS连接
 ENCOLLMENT E 
对CS.C_SE_ID = E.C_SE_ID连接
位置L 
对CS.LOC_ID = L.LOC_ID 
其中L.BLDG_CODE ='DBW'
 GROUP BY CS.C_SE_ID 
按次数排序（*）desc 
）t 
其中rownum = 1 
  

注意我使用在上更新了现代版本的连接语法，而不是其中。

如果您想要所有最小值（还有更多比一个），那么我会使用分析功能。这与您的原始查询非常相似：

  select * 
 from（select CS.C_SE_ID，count（ *）作为cnt，
 max（count（*））over（由cs.c_se_id分区）作为maxcnt 
来自COURSE_SECTION CS连接
 ENCOLLMENT E 
对CS.C_SE_ID = E .C_SE_ID加入
位置L 
对于CS.LOC_ID = L.LOC_ID 
其中L.BLDG_CODE ='DBW'
 GROUP BY CS.C_SE_ID 
按计数排序（ *）desc 
）t 
其中cnt = maxcnt; 
  

试试这个，而不是原来的查询：

< （选择CS.C_SE_ID，count（*）as（选择C_SE_ID
from（选择CS.C_SE_ID，count（*）as cnt，
max（count（*））over（由cs.c_se_id分区）作为maxcnt
从ENROLLMENT E
LOCATION L
对CS.LOC_ID = L.LOC_ID
where L.BLDG_CODE ='DBW'
GROUP BY e.C_SE_ID
）t
where cnt = maxcnt）
）;


除了修复连接外，我还删除了对 course_section 。这个表似乎没有被使用（除非用于过滤结果），并且删除它会隐含查询。

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

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_id and c_se_id are linked, I'm trying to get all the s_id that 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_id I get nothing.

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);

解决方案

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 on instead of where.

If you want all minimum 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.

这篇关于SQL组函数嵌套太深的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！