我有两张桌子。不同之处在于,归档是一个表,另一个保存当前记录。这些是记录公司销售额的表格。在这两个字段中,我们都有其他字段: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
但这样的查询会吸引我两条记录——一条是当前表,一条是归档表。我想立即从两个表中为最小和最大价格选择一个名称。我如何得到这个查询?
这里有两个选项(符合 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)