在子查询联接中ORDER BY被忽略了吗? [英] ORDER BY is being ignored in subquery join?

问题描述

我有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文件创建的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.请记住，ORDER BY子句在应用GROUP BY子句后将无效-您不能依靠事实，即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

这篇关于在子查询联接中ORDER BY被忽略了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！