从两个表中选择max，min值 [英] select max, min values from two tables

问题描述

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

I have two tables. Differ in that an archive is a table and the other holds the current record. These are the tables recording sales in the company. In both we have among other fields: id, name, price of sale. I need to select from both tables, the highest and lowest price for a given name. I tried to do with the query:

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

但是这样的查询会给我两个记录 - 当前表格，一个表格档案。我想从两个表中选择一个最小和最大价格的名字。

but such an inquiry draws me two records - one of the current table, one table archival. I want to chose a name for the smallest and the largest price immediately from both tables. How do I get this query?

推荐答案

这里有两个选项（符合MSSql）

Here's two options (MSSql compliant)

注意：UNION ALL将组合集合而不删除重复。

Note: UNION ALL will combine the sets without eliminating duplicates. That's a much simpler behavior than 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

这篇关于从两个表中选择max，min值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！