sql - 从两个表中选择最大值、最小值

Question

我有两张 table 。不同之处在于，一个存档是一个表，另一个保存当前记录。这些是记录公司销售额的表格。在这两个字段中，我们都有其他字段:id、名称、销售价格。我需要从两个表中选择给定名称的最高价和最低价。我尝试处理查询:

select name, max (price_of_sale), min (price_of_sale)
from wapzby
union
select name, max (price_of_sale), min (price_of_sale)
from wpzby
order by name

但是这样的一个查询给我画了两条记录——一条是当前表，一条是归档表。我想立即从两个表中为最小和最大价格选择一个名称。我如何获得此查询？

Answer 1

这里有两个选项(符合 MSSql)

注意:UNION ALL 将合并集合而不消除重复项。这比 UNION 简单得多。

SELECT Name, MAX(Price_Of_Sale) as MaxPrice, MIN(Price_Of_Sale) as MinPrice
FROM
(
    SELECT Name, Price_Of_Sale
    FROM wapzby
    UNION ALL
    SELECT Name, Price_Of_Sale
    FROM wpzby
) as subQuery
GROUP BY Name
ORDER BY Name

这个在组合集合之前从每个表中计算出最大值和最小值 - 这样做可能性能更高。

SELECT Name, MAX(MaxPrice) as MaxPrice, MIN(MinPrice) as MinPrice
FROM
(
    SELECT Name, MAX(Price_Of_Sale) as MaxPrice, MIN(Price_Of_Sale) as MinPrice
    FROM wapzby
    GROUP BY Name
    UNION ALL
    SELECT Name, MAX(Price_Of_Sale) as MaxPrice, MIN(Price_Of_Sale) as MinPrice
    FROM wpzby
    GROUP BY Name
) as subQuery
GROUP BY Name
ORDER BY Name