MySQL 如何在SQL查询中对LEFT JOIN的顺序进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5355585/
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
how to sort order of LEFT JOIN in SQL query?
提问by Richard Rodriguez
OK I tried googling for an answer like crazy, but I couldn't resolve this, so I hope someone will be able to help.
好吧，我疯狂地尝试在谷歌上搜索答案，但我无法解决这个问题，所以我希望有人能够提供帮助。
Let's say I have a table of users, very simple table:
假设我有一个用户表，非常简单的表：
id | userName
3 Michael
4 Mike
5 George
and I have another table of their cars and their prices.
我还有一张他们的汽车和价格表。
id | belongsToUser | carPrice
1 4 5000
2 4 6000
3 4 8000
Now what I need to do is something like this (feel free to rewrite):
现在我需要做的是这样的事情（随意重写）：
SELECT
`userName`,
`carPrice`
FROM `users`
LEFT JOIN `cars`
ON cars.belongsToUser=users.id
WHERE `id`='4'
Which returns:
返回：
Mike | 5000
But I need the most expensive car of a certain user, not the first entry found.
但是我需要某个用户最贵的车，而不是找到的第一个条目。
So question: How do I set the LEFT JOIN table to be ordered by carPrice, DESC ?
所以问题：如何设置 LEFT JOIN 表按 carPrice, DESC 排序？
回答by a'r
Try using MAXwith a GROUP BY.
尝试使用MAX了GROUP BY。
SELECT u.userName, MAX(c.carPrice)
FROM users u
LEFT JOIN cars c ON u.id = c.belongsToUser
WHERE u.id = 4;
GROUP BY u.userName;
Further information on GROUP BY
更多信息 GROUP BY
The group by clause is used to split the selected records into groups based on unique combinations of the group by columns. This then allows us to use aggregate functions (eg. MAX, MIN, SUM, AVG, ...) that will be applied to each group of records in turn. The database will return a single result record for each grouping.
group by 子句用于根据 group by 列的唯一组合将所选记录拆分为多个组。这允许我们使用聚合函数（例如 MAX、MIN、SUM、AVG 等），这些函数将依次应用于每组记录。数据库将为每个分组返回单个结果记录。
For example, if we have a set of records representing temperatures over time and location in a table like this:
例如，如果我们有一组记录代表温度随时间和位置在这样的表中：
Location Time Temperature
-------- ---- -----------
London 12:00 10.0
Bristol 12:00 12.0
Glasgow 12:00 5.0
London 13:00 14.0
Bristol 13:00 13.0
Glasgow 13:00 7.0
...
Then if we want to find the maximum temperature by location, then we need to split the temperature records into groupings, where each record in a particular group has the same location. We then want to find the maximum temperature of each group. The query to do this would be as follows:
然后如果我们想按位置找到最高温度，那么我们需要将温度记录拆分成组，其中特定组中的每个记录具有相同的位置。然后我们想要找到每组的最高温度。执行此操作的查询如下所示：
SELECT Location, MAX(Temperature)
FROM Temperatures
GROUP BY Location;
回答by vencedor
Older MySQL versions this is enough:
较旧的 MySQL 版本这就足够了：
SELECT
`userName`,
`carPrice`
FROM `users`
LEFT JOIN (SELECT * FROM `cars` ORDER BY `carPrice`) as `cars`
ON cars.belongsToUser=users.id
WHERE `id`='4'
Nowdays, if you use MariaDB the subquery should be limited.
现在，如果您使用 MariaDB，子查询应该受到限制。
SELECT
`userName`,
`carPrice`
FROM `users`
LEFT JOIN (SELECT * FROM `cars` ORDER BY `carPrice` LIMIT 18446744073709551615) as `cars`
ON cars.belongsToUser=users.id
WHERE `id`='4'
回答by MatBailie
Several other answer give the solution using MAX. In some scenarios using an agregate function is either not possilbe, or not performant.
其他几个答案给出了使用 MAX 的解决方案。在某些情况下，使用聚合函数要么不可能，要么性能不佳。
The alternative that I use a lot is to use a correlated sub-query in the join...
我经常使用的替代方法是在连接中使用相关子查询...
SELECT
`userName`,
`carPrice`
FROM `users`
LEFT JOIN `cars`
ON cars.id = (
SELECT id FROM `cars` WHERE BelongsToUser = users.id ORDER BY carPrice DESC LIMIT 1
)
WHERE `id`='4'
回答by Forgotten Semicolon
This will get you the most expensive car for the user:
这将为您提供最昂贵的汽车：
SELECT users.userName, MAX(cars.carPrice)
FROM users
LEFT JOIN cars ON cars.belongsToUser=users.id
WHERE users.id=4
GROUP BY users.userName
However, this statement makes me think that you want all of the cars prices sorted, descending:
但是，此语句让我认为您希望所有汽车价格按降序排序：
So question: How do I set the LEFT JOIN table to be ordered by carPrice, DESC ?
所以问题：如何设置 LEFT JOIN 表按 carPrice, DESC 排序？
So you could try this:
所以你可以试试这个：
SELECT users.userName, cars.carPrice
FROM users
LEFT JOIN cars ON cars.belongsToUser=users.id
WHERE users.id=4
GROUP BY users.userName
ORDER BY users.userName ASC, cars.carPrice DESC
回答by Felix Geenen
try this out:
试试这个：
SELECT
`userName`,
`carPrice`
FROM `users`
LEFT JOIN `cars`
ON cars.belongsToUser=users.id
WHERE `id`='4'
ORDER BY `carPrice` DESC
LIMIT 1
Felix
菲利克斯
