如何在SQL查询中对LEFT JOIN的顺序进行排序? [英] how to sort order of LEFT JOIN in SQL query?

问题描述

好的，我尝试使用谷歌搜索来寻找一个疯狂的答案，但是我无法解决这个问题，所以我希望有人能够提供帮助.

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'

哪个返回:

Mike | 5000

但是我需要某个用户的最昂贵的汽车，而不是找到的第一个条目.

But I need the most expensive car of a certain user, not the first entry found.

那么问题:我如何设置LEFT JOIN表由carPrice，DESC排序?

So question: How do I set the LEFT JOIN table to be ordered by carPrice, DESC ?

推荐答案

尝试将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;

---

关于GROUP BY

的更多信息

group by子句用于根据group by列的唯一组合将所选记录分为几组.然后，这使我们能够使用聚合函数(例如MAX，MIN，SUM，AVG等)，这些函数将依次应用于每组记录.数据库将为每个分组返回单个结果记录.

---

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.

例如，如果我们在这样的表中有一组记录，这些记录代表时间和位置上的温度:

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;

这篇关于如何在SQL查询中对LEFT JOIN的顺序进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！