ORDER BY 在子查询连接中被忽略?

2022-01-23 00:00:00 join sql subquery mysql sql-order-by

我有 3 个表:用户、项目和文件.以下是相关栏目:

I have 3 tables: users, projects, and files. Here's the relevant columns:

users:    [userid](int)

projects: [userid](int) [projectid](int) [modified](datetime)

files:    [userid](int) [projectid](int) [fileid](int) [filecreated](datetime)

我正在使用查询来列出所有项目，但我还想包含另一个表中的最新文件.我的方法是使用子查询来加入.

I'm using a query to list all projects, but I also want to include the most recent file from another table. My approach to this was using a subquery to join on.

这是我想出的，但我的问题是它返回了最旧的文件:

Here's what I came up with, but my problem is that it's returning the oldest file:

SELECT * FROM projects
INNER JOIN users ON projects.userid = users.userid
JOIN (SELECT filename,projectid FROM files
      GROUP BY files.projectid
      ORDER BY filecreated DESC) AS f
ON projects.projectid = f.projectid
ORDER BY modified DESC

我认为 ORDER BY filecreated DESC 会解决这个问题，但它似乎完全被忽略了.

I would think ORDER BY filecreated DESC would solve this, but it seems completely ignored.

我对 SQL 还很陌生，也许我的方法不对?

I'm fairly new to SQL, perhaps I'm not approaching this the right way?

推荐答案

你的问题在这里，在你的子查询中:

Your problem is here, in your subquery:

(SELECT filename,projectid FROM files
      GROUP BY files.projectid
      ORDER BY filecreated DESC) AS f

由于您使用的是那种混合分组和非分组列，我假设您使用的是 MySQL.请记住，在应用 GROUP BY 子句后，ORDER BY 子句将有 no effect - 你不能相信 MySQL 允许这样的语法(一般来说，在普通 SQL 中，这根本就是不正确的查询).

since you're using that kind of mixing grouped and non-grouped columns I assume you're using MySQL. Remember, ORDER BY clause will have no effect after applying GROUP BY clause - you can not rely on the fact, that MySQL allows such syntax (in general, in normal SQL this is incorrect query at all).

要解决此问题，您需要在子查询中获取格式正确的记录.可以这样做，例如:

To fix that you need to get properly formed records in your subquery. That could be done, for example:

SELECT
  files.filename,
  files.projectid
FROM
  (SELECT  
    MAX(filecreated) AS max_date, 
    projectid 
  FROM 
    files 
  GROUP BY 
    projectid) AS files_dates
  LEFT JOIN
    files 
      ON files_dates.max_date=files.filecreated AND files_dates.projectid=files.projectid

相关文章