oracle SQL 组函数嵌套太深
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20035212/
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 user3001575
I want to make a SQL query which finds the pnameof parts which are the least repeated in supplier_parts_shipment.
我想打一个SQL查询，该查询发现pname这是最少的重复部分 supplier_parts_shipment。
Table 1 supplier (sno, sname, city)
表 1 供应商（sno, sname, city）
1, ahmad, jeddah
2,kaled,med
3,njwa,med
Table 2 parts (pno, pname, color)
表 2 部分（pno、pname、颜色）
1, laptop, red
2,keybord,blue
Table 3 supplier_parts_shipment (shno, sno, pno, date)
表 3 supply_parts_shipment (shno, sno, pno, date)
1,1,1,2014
2,2,1,2014
3,3,2,2014
I tried something like this:
我试过这样的事情：
SELECT pname
, min(count(pno))
FROM parts
WHERE pno IN (SELECT pno
FROM supplier_parts_shipment
group by
pname
HAVING min(count(pno))
)
SQL> /
pno IN(SELECT pno FROM supplier_parts_shipment group by pname HAVING min(count(pno))))
*
ERROR at line 2:
ORA-00935: group function is nested too deeply
回答by Brett Schneider
min(count(pno))
makes no sense. that is why you are getting the error. try:
没有意义。这就是您收到错误的原因。尝试：
select parts.pname
, subse.c
from (select pno
, dense_rank() over (order by c asc) r
, c
from (select pno
, count(pno) as c
from supplier_parts_shipment
group by
pno
)
) subse
inner join parts
on (parts.pno = subse.pno)
where subse.r = 1
the innermost select counts the supplier_parts_shipment for pno, the second level finds the pno that has the least count from the innermost. the outermost select then joins pname from parts and filters to the one that has the least repeats.
最里面的选择计算pno的supplier_parts_shipment，第二级从最里面找到计数最少的pno。最外面的选择然后将 pname 从零件和过滤器连接到重复最少的那个。
回答by tyh
I would have gone about it in a different manner.
我会以不同的方式处理它。
First create a query that shows the counts of shipments by pname ordered in ascending values. Use that as a subquery and pick the first.
首先创建一个查询，以按升序排列的 pname 显示发货计数。将其用作子查询并选择第一个。
SELECT * FROM (
SELECT COUNT(sps.pno), p.pname
FROM supplier_parts_shipment sps
JOIN parts p on sps.pno = p.pno
GROUP BY pname
ORDER BY COUNT(sps.pno) ASC)
WHERE ROWNUM = 1
回答by MT0
If there are multiple parts which are least frequent and you want all of them then:
如果有多个最不常见的部分并且您想要所有这些部分，那么：
WITH pno_frequencies AS (
SELECT pno,
COUNT(1) AS pno_cnt
FROM supplier_parts_shipment
GROUP BY pno
),
least_frequent_pnos AS (
SELECT pno
FROM pno_frequencies
WHERE pno_cnt = ( SELECT MIN( pno_cnt ) FROM pno_frequencies )
)
SELECT pname
FROM parts p
WHERE EXISTS (SELECT 1
FROM least_frequent_pnos f
WHERE p.pno = f.pno
);
If you only want a single part regardless of whether there are multiple parts with the same minimum frequency then:
如果您只需要一个零件，而不管是否有多个具有相同最小频率的零件，那么：
WITH pno_frequencies AS (
SELECT pno,
COUNT(1) AS pno_cnt
FROM supplier_parts_shipment
GROUP BY pno
ORDER BY pno_cnt ASC
),
least_frequent_pno AS (
SELECT pno
FROM pno_frequencies
WHERE ROWNUM = 1
)
SELECT pname
FROM parts p
WHERE EXISTS (SELECT 1
FROM least_frequent_pno f
WHERE p.pno = f.pno
);
回答by Tejash
If you are using oracle 12cor higher then it will be quite easy with row limiting clauseas following:
如果您使用的是oracle 12c或更高版本，那么它会很容易，row limiting clause如下所示：
Select p.pname, count(1) as cnt
From parts p
Join supplier_parts_shipment sps
On sps.pno = p.pno
Group by p.name
Order by cnt
Fetch first row with ties
For more information about row limiting clause refer this document.
有关行限制子句的更多信息，请参阅此文档。
Cheers!!
干杯！！
