MySQL SQL：在多列上使用 GROUP BY 和 MAX
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4045609/
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
SQL : Using GROUP BY and MAX on multiple columns
提问by Charles
I have an issue with an SQL Query. Lets take this example data
我有一个 SQL 查询问题。让我们以这个示例数据为例
itemID catID attrib1 attrib2
1 1 10 5
2 1 10 7
3 1 5 10
4 2 18 15
I want to return the best item for each category (with attrib1 having priority over attrib2)
我想为每个类别返回最好的项目（attrib1 优先于 attrib2）
Obviously, SELECT catID, MAX(attrib1), MAX(attrib2) FROM test_table GROUP BY catIDdoesn't work since it will return 10 & 10 for the 1st cat.
显然，SELECT catID, MAX(attrib1), MAX(attrib2) FROM test_table GROUP BY catID它不起作用，因为它会为第一只猫返回 10 和 10。
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
那么无论如何要告诉 MySQL 从 attrib2 行中选择最大值，但只考虑 attrib1 也是最大值的那些？即返回以下数据
catID attrib1 attrib2
1 10 7
2 18 15
采纳答案by Guffa
You can get the best attrib1 values, and then join in the attrib2 values and get the best of those for each attrib1 value:
您可以获得最好的 attrib1 值，然后加入 attrib2 值并为每个 attrib1 值获取最好的值：
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
回答by OMG Ponies
Use:
用：
SELECT x.catid,
x.max_attrib1 AS attrib1,
(SELECT MAX(attrib2)
FROM YOUR_TABLE y
WHERE y.catid = x.catid
AND y.attrib1 = x.max_attrib1) AS attrib2
FROM (SELECT t.catid,
MAX(t.attrib1) AS max_attrib1
FROM YOUR_TABLE t
GROUP BY t.catid) x
回答by Andrew
SELECT tt.catId, tt.attrib1, MAX(tt.attrib2)
FROM test_table tt
GROUP BY tt.catID, tt.attrib1
WHERE tt.attrib1 = (SELECT MAX(t2.attrib1) FROM test_table t2 WHERE t2.catID = tt.catID)
回答by Martijn
SELECT catID, max1, max2 FROM
((SELECT Max(attrib1) as max1, catID GROUP BY attrib1) as t1
INNER JOIN
(SELECT MAX(attrib2) as max2, catID GROUP BY attrib2) as t2
ON t1.catID = t2.catID) as t3
