查询SPJ表中每个供应商“供应零件总重量”最重的记录及“供应零件总重量”

查询每个供应商供应零件总重量最重的记录 - 三种SQL实现方法对比

问题描述

我们需要从SPJ表（供应关系表）和P表（零件表）中，找出每个供应商(SNO)供应零件总重量最大的记录，其中：

• 供应零件总重量 = 零件重量(WEIGHT) × 供应数量(QTY)
• 结果需要包含原SPJ表的所有字段以及计算出的总重量

方法一：UNION拼接法

SELECT SPJ.*,QTY_WEIGHT
FROM SPJ,(SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO ) AS NEW
WHERE SPJ.SNO = 'S1' AND SPJ.SNO = NEW.SNO AND SPJ.PNO = NEW.PNO AND SPJ.JNO = NEW.JNO AND QTY_WEIGHT = 
(SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S1' ) RR )

UNION

SELECT SPJ.*,QTY_WEIGHT
FROM SPJ,(SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO ) AS NEW
WHERE SPJ.SNO = 'S2' AND SPJ.SNO = NEW.SNO AND SPJ.PNO = NEW.PNO AND SPJ.JNO = NEW.JNO AND QTY_WEIGHT = 
(SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S2' ) RR )

UNION

SELECT SPJ.*,QTY_WEIGHT
FROM SPJ,(SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO ) AS NEW
WHERE SPJ.SNO = 'S3' AND SPJ.SNO = NEW.SNO AND SPJ.PNO = NEW.PNO AND SPJ.JNO = NEW.JNO AND QTY_WEIGHT = 
(SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S3' ) RR )

UNION

SELECT SPJ.*,QTY_WEIGHT
FROM SPJ,(SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO ) AS NEW
WHERE SPJ.SNO = 'S4' AND SPJ.SNO = NEW.SNO AND SPJ.PNO = NEW.PNO AND SPJ.JNO = NEW.JNO AND QTY_WEIGHT = 
(SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S4' ) RR )

UNION

SELECT SPJ.*,QTY_WEIGHT
FROM SPJ,(SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO ) AS NEW
WHERE SPJ.SNO = 'S5' AND SPJ.SNO = NEW.SNO AND SPJ.PNO = NEW.PNO AND SPJ.JNO = NEW.JNO AND QTY_WEIGHT = 
(SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S5' ) RR )

思路

先创建一个 new 表：这个表的特点是会在原表中加入 我们需要的 零件总重量的属性

SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO 

再创建一个 RR 表：这个表的特点是会在返回 对应的 sno 的零件总重量最大值

(SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S5' ) RR

于是我们只需要在对应的 sno 中选择零件最大值即可，也就是选择的条件是 sno = 5 的零件中 QTY_WEIGHT 与 sno = 5 的最大值相同即可

遇到的问题

这是我第一个写的版本，我不知道该如何使用 max 函数

• max 函数不可以在 where 中直接使用，只能出现在 select 中，如果需要在 where 中使用，就需要用到 where 子查询

SELECT MAX(QTY_WEIGHT1) FROM (SELECT SPJ.*, P.WEIGHT * SPJ.QTY AS QTY_WEIGHT1
    FROM SPJ,P
    WHERE SPJ.PNO = P.PNO AND SPJ.SNO = 'S5'

• 另外 在 FROM 中使用子查询，即使用 “派生表” 时，必须给派生表取个别名（不管用不用），这是硬性要求，不写会报错

Error Code: 1248. Every derived table（派生表、导出表）must have its own alias

方法二：GROUP BY分组法

SELECT AA.*
FROM (
    SELECT SPJ.*, (P.WEIGHT * SPJ.QTY) QTY_WEIGHT 
    FROM SPJ, P 
    WHERE SPJ.PNO = P.PNO
) AA, 
(
    SELECT A.SNO, MAX(M) AS QTY_WEIGHT 
    FROM (
        SELECT SPJ.*, (P.WEIGHT * SPJ.QTY) M 
        FROM SPJ, P 
        WHERE SPJ.PNO = P.PNO
    ) A
    GROUP BY A.SNO
) B
WHERE AA.SNO = B.SNO AND AA.QTY_WEIGHT = B.QTY_WEIGHT
ORDER BY AA.SNO

思路

关键是这个表格：

SELECT A.SNO, MAX(M) AS QTY_WEIGHT FROM (SELECT SPJ.*, (P.WEIGHT * SPJ.QTY) M FROM SPJ, P WHERE SPJ.PNO = P.PNO) A  -- 计算出重量
GROUP BY A.SNO

运行结果如下

SNO	QTY_WEIGHT
S1	8400
S2	16000
S3	2800
S4	9000
S5	15000

所以我们只需要找到对应的 sno 中此表相同的 QTY_WEIGHT 的即可

这个方法不是我想到的，我看到的时候觉得相当天才 —— 不亏是我的天才男朋友，总是能令我眼前一亮。

遇到的问题

不知道 group by 是如何工作的，也不是很明白 max 函数是如何操作的，哈哈哈还多亏了小赖呢，不然又不知道要弄多久。

• 若无 group by

SNO	QTY_WEIGHT
S2	16000

方法三：相关子查询法

SELECT SS.*
FROM (
    SELECT SPJ.*, SPJ.QTY * P.WEIGHT AS QTY_WEIGHT
    FROM SPJ 
    JOIN P USING (PNO)   
) AS SS
WHERE SS.QTY_WEIGHT = (
    SELECT MAX(SPJ.QTY * P.WEIGHT)
    FROM SPJ 
    JOIN P USING (PNO) 
    WHERE SS.SNO = SPJ.SNO
)
ORDER BY SS.SNO

思路

什么是相关子查询：

给主查寻的表建一个别名，并且在子查询中使用这个别名。

1. 创建SS表（计算每条记录的零件总重量）：

   SELECT SPJ.*, SPJ.QTY * P.WEIGHT AS QTY_WEIGHT
   FROM SPJ 
   JOIN P USING (PNO)
   

   这个子查询为每条供应记录计算零件总重量

2. 使用相关子查询筛选记录：

   WHERE SS.QTY_WEIGHT = (
       SELECT MAX(SPJ.QTY * P.WEIGHT)
       FROM SPJ 
       JOIN P USING (PNO) 
       WHERE SS.SNO = SPJ.SNO
   )
   

   对于SS表中的每条记录，子查询计算该供应商（SS.SNO = SPJ.SNO）所有记录中的最大零件总重量，然后筛选出总重量等于该最大值的记录

性能对比

1. UNION方法：最差，需要为每个供应商单独执行查询
2. GROUP BY方法：较好，通常只需要扫描表2-3次
3. 相关子查询：中等，取决于数据分布和索引情况