SQL组函数嵌套太深(SQL group function nested too deeply)

我想打一个SQL查询，该查询发现pname这是最少的重复部分的supplier_parts_shipment 。

表1供应商（SNO，SNAME，市）

1, ahmad, jeddah
2,kaled,med
3,njwa,med

表2份（PNO，PNAME，颜色）

1, laptop, red
2,keybord,blue

表3 supplier_parts_shipment（shno，SNO，PNO，日期）

1,1,1,2014
2,2,1,2014
3,3,2,2014

我想是这样的：

  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

Answer 1:

我会以不同的方式去了吧。

首先创建一个查询，显示为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

Answer 2:

如果有多个部分，它们至少频繁，你希望所有的人，则：

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

如果你只想要一个部分，不管是否有与当时相同的最低频率的多个部分：

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

SQLFIDDLE

Answer 3:

min(count(pno)) 

没有意义。 这就是为什么你所得到的错误。 尝试：

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

最里面选择计数supplier_parts_shipment为PNO，第二级认定，具有从最里面的至少计数PNO。 最外面的选择然后加入从部件和过滤器PNAME到具有至少重复的一个。

文章来源: SQL group function nested too deeply