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

Question

我有两张桌子。不同之处在于，归档是一个表，另一个保存当前记录。这些是记录公司销售额的表格。在这两个字段中，我们都有其他字段：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

Answer 2

在 SQL Server 中，您可以使用子查询：

SELECT  [name],
        MAX([price_of_sale]) AS [MAX price_of_sale],
        MIN([price_of_sale]) AS [MIN price_of_sale]
FROM (
    SELECT  [name],
            [price_of_sale]
    FROM    [dbo].[wapzby]
    UNION
    SELECT  [name],
            [price_of_sale]
    FROM    [dbo].[wpzby]
) u
GROUP BY [name]
ORDER BY [name]

Answer 3

这更像你想要的吗？

SELECT 
    a.name,
    MAX (a.price_of_sale), 
    MIN (a.price_of_sale) ,  
    b.name, 
    MAX (b.price_of_sale), 
    MIN (b.price_of_sale) 
FROM 
    wapzby a, 
    wpzby b
ORDER BY 
    a.name 

它未经测试，但应该在一行上返回所有记录，而不需要联合

Answer 4

SELECT MAX(value) FROM tabl1 UNION SELECT MAX(value) FROM tabl2;
SELECT MIN(value) FROM tabl1 UNION SELECT MIN(value) FROM tabl2;

Answer 5

SELECT (SELECT MAX(value) FROM table1 WHERE trn_type='CSL' andtil='TILL01') as summ, (SELECT MAX(value) FROM table2WHERE trn_type='CSL' andtil='TILL01') as summ_hist