SQL 从两个表中选择最大值、最小值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7877062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select max, min values from two tables
提问by BKl
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:
我有两张桌子。不同之处在于存档是一个表，另一个保存当前记录。这些是记录公司销售额的表格。在这两个领域中，我们都有其他字段：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
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?
但是这样的查询吸引了我两条记录-一条是当前表，一条是存档表。我想立即从两个表中为最小和最大价格选择一个名称。我如何得到这个查询？
回答by Amy B
Here's two options (MSSql compliant)
这是两个选项（符合 MSSql）
Note: UNION ALL will combine the sets without eliminating duplicates. That's a much simpler behavior than UNION.
注意：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
This one figures out the max and min from each table before combining the set - it may be more performant to do it this way.
这个在组合集合之前计算出每个表的最大值和最小值 - 这样做可能会更高效。
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
回答by Tom Hunter
In SQL Server you could use a subquery:
在 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]
回答by Purplegoldfish
Is this more like what you want?
这更像你想要的吗？
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
It's untested but should return all your records on one row without the need for a union
它未经测试，但应该在一行中返回所有记录，而无需联合
回答by Karim Elshaweish
SELECT MAX(value) FROM tabl1 UNION SELECT MAX(value) FROM tabl2;
SELECT MIN(value) FROM tabl1 UNION SELECT MIN(value) FROM tabl2;
