查询:找到已使用最大零件数量的项目名称

Question

我正在做一个分配，其中我创建了三个表"项目"，" part"one_answers" use"。表的结构如下：

 SQL> CREATE TABLE PROJECT
  2  (
  3  PNO VARCHAR2(5) PRIMARY KEY,
  4  PNAME VARCHAR2(15) NOT NULL,
  5  CITY VARCHAR2(15) NOT NULL
  6  );
SQL> CREATE TABLE PART
  2  (
  3  PID VARCHAR2(5) PRIMARY KEY,
  4  PNAME VARCHAR2(15) NOT NULL,
  5  COLOR VARCHAR2(15) NOT NULL
  6  );
SQL> CREATE TABLE USE
  2  (
  3  PNO VARCHAR2(5),
  4  PID VARCHAR2(5),
  5  QTY NUMBER CHECK(QTY >5 AND QTY<100),
  6  FOREIGN KEY(PNO) REFERENCES PROJECT,
  7  FOREIGN KEY(PID) REFERENCES PART,
  8  PRIMARY KEY(PNO,PID)
  9  );

我想编写一个查询来找出使用最多产品的项目名称。

我能够编写以下查询，以返回每个项目使用的零件数量。

SQL> SELECT PNO, SUM(QTY)
  2  FROM USE
  3  GROUP BY PNO;
PNO     SUM(QTY)
----- ----------
P4           205
P1            95
P2            95
P3            20

Answer 1

您可以在下面尝试 -

select * from
(
SELECT PNO, SUM(QTY) as totalqty
  FROM USE
  GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1

Answer 2

尝试如下

   with cte as (
     SELECT PNO, SUM(QTY) as q
       FROM USE
       GROUP BY PNO
)  select * from cte where cte.q= (select max(q) from cte)

Answer 3

我们可以在此处尝试使用RANK，以捕获第一名的所有关系：

WITH cte AS (
    SELECT p.PNAME, SUM(u.QTY) AS QTY,
        RANK() OVER (ORDER BY SUM(u.QTY) DESC) rnk
    FROM PROJECT p
    INNER JOIN USE u
        ON p.PNO = u.PNO
    GROUP BY p.PNO, p.PNAME
)
SELECT PNAME, QTY
FROM cte
WHERE rnk = 1
ORDER BY PNAME;