sql - 将 MAX 与多个表一起使用

Question

我有四个表:products、pc、laptop 和 printer。

    Products(maker, model, type)
    PC(code, model, speed, hd, cd, price)
    Laptop(code, model, speed, ram, hd, screen, price)
    Printer(code, model, color, type price)

我需要找到价格最高的产品(PC、笔记本电脑或打印机)的型号。这不适用于 case 语句，因为如果两个型号的价格最高，则都需要显示，并且使用 case 将仅选择一个然后退出 case 语句。我想使用 UNION 运算符来执行此操作，但我不确定该怎么做。这是我目前所拥有的:

SELECT model FROM 
(SELECT model, MAX(price) FROM 
(SELECT model, price FROM Pc UNION ALL SELECT model, price FROM Laptop UNION ALL 
 SELECT model, price FROM Printer) 
 GROUP BY model)

但这是不正确的语法，我不确定为什么。有什么想法吗？

Answer 1

Select datatable.model as price from (
    Select P.model,P.price from PC P where P.price=(select Max(Q.price) from PC Q)
    Union 
    Select P.model,P.price from Laptop P where P.price=(select Max(Q.price) from Laptop Q)
    Union 
    Select P.model,P.price from Printer P where P.price=(select Max(Q.price) from Printer Q)
) as datatable where datatable.price=(
    Select Max(newtable.price) as price from (
        Select P.model,P.price from PC P where P.price=(select Max(Q.price) from PC Q)
        Union 
        Select P.model,P.price from Laptop P where P.price=(select Max(Q.price) from Laptop Q)
        Union 
        Select P.model,P.price from Printer P where P.price=(select Max(Q.price) from Printer Q)) as newtable)