SELECT COUNT 与 ORDER BY 混乱 [英] SELECT COUNT messes with ORDER BY

问题描述

我有一个问题，我真的不明白.查询

I have a problem I really don't understand. The query

SELECT
    f.*,
    ftv.content,
    ftv.updated,
    COUNT(ftv.file_number) AS versions
FROM 
    files as f
INNER JOIN 
    files_text_versions AS ftv
ON 
    ftv.file_number = f.file_number
WHERE 
    f.file_number = '%s'
ORDER BY 
    ftv.updated DESC
LIMIT 1

完全忽略 ORDER BY 语句，但如果我删除

totally ignores the ORDER BY statement, but if I remove the

COUNT(ftv.file_number) AS versions

查询运行良好并按预期排序.

the query runs fine and orders as it should.

这是为什么，我该如何解决?

Why is that, and how do I fix it?

SQL 有同样的问题，但只有一张表:

SQL fiddle with same problem but only one table:

http://sqlfiddle.com/#!2/c8f124/2

推荐答案

您的 count(*) 将查询转换为聚合查询.如果没有 group by，它只返回一行.虽然在其他数据库中你会得到一个错误，但 MySQL 允许这种语法.

Your count(*) turns the query into an aggregation query. Without a group by, it just returns one row. Although in other databases you would get an error, MySQL allows this syntax.

您可以通过添加 group by 来解决此问题.但是，ftv 中的其他两列就会出现问题.您可以通过巧妙的聚合获得您想要的值:

You can fix this by adding a group by. However, you then have a problem with the other two columns from ftv. You can get the values you want with clever aggregation:

SELECT f.*,
       substring_index(group_concat(ftv.content order by ftv.updated desc), ',', 1) as content,
       MAX(ftv.updated) as updated,
       COUNT(ftv.file_number) AS versions
FROM files f INNER JOIN 
     files_text_versions AS ftv
     ON ftv.file_number = f.file_number
WHERE f.file_number = '%s'
GROUP BY t.file_number
ORDER BY updated DESC
LIMIT 1;

这篇关于SELECT COUNT 与 ORDER BY 混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！