如何对SQL查询中的左连接排序?

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 ?

那么问题是:如何将左连接表设置为由carPrice, DESC排序?

5 个解决方案

#1

Try using MAX with a GROUP BY.

试着用MAX和一个组。

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

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子句用于根据组按列的唯一组合将所选记录分割为组。这样我们就可以使用聚合函数(如。这将依次应用于每组记录。数据库将为每个分组返回单个结果记录。

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;

#2

   SELECT
      `userName`,
      `carPrice`
   FROM `users`
   LEFT JOIN (SELECT * FROM `cars` ORDER BY `carPrice`) as `cars`
   ON cars.belongsToUser=users.id
   WHERE `id`='4'

#3

Several other answer give the solution using MAX. In some scenarios using an agregate function is either not possilbe, or not performant.

其他几个答案用MAX给出答案。在某些情况下，使用agregate函数是不可能的，或者不能实现。

The alternative that I use a lot is to use a correlated sub-query in the join...

我经常使用的另一种方法是在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'

#4

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 ?

那么问题是:如何将左连接表设置为由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

#5

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

费利克斯