MySQL:使用 Union 将多个 Select 查询拆分到各自的表中

Question

我有下表:

产品(制造商、型号、类型)

PC(型号、速度、内存、高清、价格)

笔记本电脑(型号、速度、内存、高清、屏幕、价格)

打印机(型号、颜色、类型、价格)

我需要编写一个查询，返回每个制造商生产的所有产品的平均价格，但前提是该平均值 >= 200，按价格降序排列。

我尝试了两种不同的方法，两种方法都非常接近，但不完全是我需要的:

(SELECT maker, AVG(price) as a
FROM Product NATURAL JOIN PC
WHERE price >= 200
GROUP BY maker)

UNION

(SELECT maker, AVG(price) as b
FROM Product NATURAL JOIN Laptop
WHERE price >= 200
GROUP BY maker)

UNION

(SELECT maker, AVG(price) as c
FROM Product NATURAL JOIN Printer
WHERE price >= 200
GROUP BY maker)

ORDER BY a;

上面给出了每个制造商为其生产的所有产品制定的平均价格，但它全部在一列中，因此您无法直观地看出每个平均值与什么产品相关。

SELECT maker,

(SELECT AVG(price)
FROM PC
WHERE price >= 200) as 'Average Cost of PCs',

(SELECT AVG(price)
FROM Laptop
WHERE price >= 200
GROUP BY maker) as 'Average Cost of Laptops',

(SELECT AVG(price)
FROM Printer
WHERE price >= 200
GROUP BY maker) as 'Average Cost of Printers'

FROM Product
GROUP BY maker;

上面成功地为每种类型的产品提供了自己的列，并且还为所有制造商提供了一个列，但它在各自的列中给出了所有 PC、打印机和笔记本电脑的平均成本，而不是由制造商制造的每种产品的平均成本。与它平行的制造商。

我不确定哪一个更接近答案，但我碰壁了，我不知道该怎么办。如果我能让第一个代码分成不同的列，那就是正确的。如果我能让第二个正确地平均那就是对的。

我对 Stack Overflow 很陌生，所以如果我没有以正确的格式提出这个问题，我深表歉意

Answer 1

您可以对“详细”表数据进行 UNION，然后连接到该数据，然后使用所谓的条件聚合(聚合函数忽略空值)来获取平均值:

SELECT p.maker
   , AVG(CASE WHEN d.Type = 'PC' THEN d.price ELSE NULL END) AS pcAvg
   , AVG(CASE WHEN d.Type = 'Laptop' THEN d.price ELSE NULL END) AS laptopAvg
   , AVG(CASE WHEN d.Type = 'Printer' THEN d.price ELSE NULL END) AS printerAvg
FROM Product AS p
LEFT JOIN (
   SELECT model, price, 'PC' AS Type FROM PC
   UNION SELECT model, price, 'Laptop' AS Type FROM Laptop
   UNION SELECT model, price, 'Printer' AS Type FROM Printer
) AS d ON p.model = d.model
GROUP BY p.maker

如果您希望价格平均值 >= 200，您可以在联合子查询中将其过滤掉，或者向每个 WHEN 添加 AND d.price >= 200。

如果您只想要平均值 >= 200，则需要像这样包装上面的查询:

SELECT q.maker
   , CASE WHEN q.pcAvg >= 200 THEN q.pcAvg ELSE NULL END AS pcAvg
   , CASE WHEN q.laptopAvg >= 200 THEN q.laptopAvg ELSE NULL END AS laptopAvg 
   , CASE WHEN q.printerAvg >= 200 THEN q.printerAvg ELSE NULL END AS printerAvg 
FROM (the query above) AS q
;

如果值 <= 200，则不能省略列，只能给出不同的值。

旁注:您实际上可以从 CASE 语句中省略 ELSE NULL，缺少 else 意味着 else null；我只是为了清楚地说明示例和意图而明确表达。