SQL:在多列上使用GROUP BY和MAX [英] SQL : Using GROUP BY and MAX on multiple columns
问题描述
我在使用SQL查询时遇到问题.让我们以这个示例数据为例
I have an issue with an SQL Query. Lets take this example data
itemID catID attrib1 attrib2
1 1 10 5
2 1 10 7
3 1 5 10
4 2 18 15
我想返回每个类别的最佳商品(attrib1优先于attrib2)
I want to return the best item for each category (with attrib1 having priority over attrib2)
很显然,SELECT catID, MAX(attrib1), MAX(attrib2) FROM test_table GROUP BY catID
不起作用,因为它将返回10& ;;.第一只猫10.
Obviously, SELECT catID, MAX(attrib1), MAX(attrib2) FROM test_table GROUP BY catID
doesn't work since it will return 10 & 10 for the 1st cat.
所以有什么办法告诉MySQL从attrib2行中选择最大值,而仅考虑attrib1也是最大值的情况吗?即返回以下数据
So is there anyway to tell MySQL to select max value from attrib2 row but only consider the ones where attrib1 is also max value ? i.e return the following data
catID attrib1 attrib2
1 10 7
2 18 15
推荐答案
您可以获取最佳的attrib1值,然后加入attrib2值并为每个attrib1值获取最佳值:
You can get the best attrib1 values, and then join in the attrib2 values and get the best of those for each attrib1 value:
select t2.catID, t2.attrib1, max(t2.attrib2)
from
(
select catID, max(attrib1) as attrib1
from test_table
group by catID
) t1
inner join test_table t2 on t2.catID = t1.catID and t2.attrib1 = t1.attrib1
group by t2.catID, t2.attrib1
这篇关于SQL:在多列上使用GROUP BY和MAX的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!