10-77 spj-查询各工程项目使用所提供零件最多的供应商(详细解答)

本题目要求编写SQL语句，在SPJ数据库中，查询各工程项目使用所提供零件最多的供应商

提示：请使用SELECT语句作答。

如统计各工程项目的各供应商提供的零件数量和为：

则每个工程项目的使用所提供最多零件数量的供应商分别为：

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `j` (  --  工程项目表
  `jno` char(3) NOT NULL,-- 工程项目号
  `jname` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`jno`)
);
CREATE TABLE `p` (  -- 零件表
  `pno` char(3) NOT NULL,  -- 零件号
  `pname` varchar(10) DEFAULT NULL,
  `color` char(2) DEFAULT NULL,
  `weight` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`pno`)
);
CREATE TABLE `s` (  -- 供应商表
  `sno` char(3) NOT NULL,  -- 供应商号
  `sname` varchar(10) DEFAULT NULL,
  `status` char(2) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sno`)
);
CREATE TABLE `spj` (  -- 零件供应表
  `sno` char(3) NOT NULL,
  `pno` char(3) NOT NULL,
  `jno` char(3) NOT NULL,
  `qty` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`sno`,`pno`,`jno`),
  CONSTRAINT `fk_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`),
  CONSTRAINT `fk_pno` FOREIGN KEY (`pno`) REFERENCES `p` (`pno`),
  CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

s表：

p表：

j表：

spj表：

输出样例:

请在这里给出输出样例。例如：

第一步 ：找到各个工程各供应商号所用零件总和

select jno,sno,sum(qty) as qty
from spj
group by jno,sno

第二步 ： 在第一步的基础上，找到各工程里面所用零件最多的供应商

select jno,max(qty) as qty
from
(
select jno,sno,sum(qty) as qty
from spj
group by jno,sno
) as a
group by jno

最后一步 ： 寻找步骤1跟步骤2的交集即为解

select b.jno,b.sno,b.qty
from
(
    select jno,sno,sum(qty) as qty
    from spj
    group by jno,sno
) as b,
(
    select jno,max(qty) as qty
    from
    (
        select jno,sno,sum(qty) as qty
        from spj
        group by jno,sno
    ) as a
    group by jno
) as c
where b.jno = c.jno and b.qty = c.qty