mysql - 在 MySQL SELECT 中使用 LIMIT 更改 ORDER BY 子句会导致行集不一致

Question

今天我很惊讶地发现以下 MySQL 查询的结果不一致:

SELECT Research.Focus、Research.Media、Country.Name、GROUP_CONCAT(DISTINCT AskMethod.Name ORDER BY ResearchAskMethod.MethodID SEPARATOR ', ') as AskMethodName、Research.ResearchDate、ResearchID FROM AskMethod INNER JOIN ( (Country INNER JOIN Research ON Country.CountryID = Research.CountryID) INNER JOIN ResearchAskMethod ON Research.ResearchID = ResearchAskMethod.ResearchID) ON AskMethod.MethodID = ResearchAskMethod.MethodID WHERE Research.ResearchID=ResearchAskMethod.ResearchID AND Research.ResearchDate=1996 GROUP BY Research.ResearchID ORDER BY Country.Name、Research.Media、AskMethodName、Research.ResearchDate DESC LIMIT 0、5；

此查询给了我 5 行:ResearchID: 18, 17, 10, 7, 13。

如果我只是将 DESC 添加到第一个 ORDER BY 参数，使其变为:

...按国家/地区.名称 DESC、研究.媒体...排序，

此查询为我提供了一组不同的 5 行:ResearchID: 8, 14, 9, 13, 7

ResearchID 是 Research 表的主键。

如果我只是更改 ORDER BY 参数的顺序，它也会给我一个不同的集合，例如:

... 按 Research.Media、Country.Name、AskMethodName 排序...

您能帮我了解一下发生了什么吗？

下面由 @Kiley 回答，这是修复后的查询:

SELECT * 
FROM (
    SELECT Research.Focus, Research.Media, Country.Name, GROUP_CONCAT( DISTINCT ValMethod.Name
    ORDER BY ResearchValMethod.MethodID
    SEPARATOR  ', ' ) AS ValMethodName, Research.ResearchDate, Research.ResearchID
    FROM ValMethod
    INNER JOIN (
        (
        Country
        INNER JOIN Research ON Country.CountryID = Research.CountryID
        )
    INNER JOIN ResearchValMethod ON Research.ResearchID = ResearchValMethod.ResearchID
    ) ON ValMethod.MethodID = ResearchValMethod.MethodID
    WHERE Research.ResearchID = ResearchValMethod.ResearchID
    AND Research.ResearchDate = 1996
    GROUP BY Research.ResearchID
    ORDER BY Country.Name, Research.Media, ValMethodName, Research.ResearchDate
) AS Result
ORDER BY Result.Name, Result.Media, ValMethodName, Result.ResearchDate DESC

Answer 1

我在这里的回答主要针对 SQL Server，但这个概念也适用于 MySQL。如果您对此表示怀疑，您应该运行我的测试并亲自验证。

我认为这里的问题是，您希望按 ResearchDate 排序时获得前五个结果，然后您希望按降序对这些结果进行排序。 According to PinalDave SELECT TOP X子句(其功能相当于 LIMIT 功能的子集)是 SELECT 逻辑处理中的最后一步。查询，因此它获取第一个 X结果已经是ORDER之后编辑。

如果您对处理顺序感到好奇，我在 MySQL Reference Manual 上找到了这个:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

这是一个简单的示例，它将演示我认为您遇到的问题以及如何解决它:

CREATE TABLE [Test] (
    Number INT PRIMARY KEY
);

INSERT INTO [Test] VALUES (1);
INSERT INTO [Test] VALUES (2);
INSERT INTO [Test] VALUES (3);
INSERT INTO [Test] VALUES (4);
INSERT INTO [Test] VALUES (5);
INSERT INTO [Test] VALUES (6);
INSERT INTO [Test] VALUES (7);
INSERT INTO [Test] VALUES (8);
INSERT INTO [Test] VALUES (9);
INSERT INTO [Test] VALUES (10);

这将设置您的数据。现在运行以下查询并检查输出:

SELECT TOP 5 * FROM Test ORDER BY Number;

在 MySQL 中:

SELECT * FROM Test ORDER BY Number LIMIT 5;

此查询将产生以下结果集:

1
2
3
4
5

现在，看看这里的区别:

SELECT TOP 5 * FROM Test ORDER BY Number DESC;

在 MySQL 中:

SELECT * FROM Test ORDER BY Number DESC LIMIT 5;

产品:

10
9
8
7
6

注意到不同的结果集了吗？这就是您现在遇到的问题。相反，您需要做的是在子查询中选择所需的结果，然后在外部查询中对这些结果进行排序。

SELECT * FROM (
    SELECT TOP 5 * FROM Test ORDER BY Number
) AS MyTest ORDER BY Number DESC

最后，在 MySQL 中:

SELECT * FROM (
    SELECT * FROM Test ORDER BY Number LIMIT 5
) AS MyTest ORDER BY Number DESC

结果？

5
4
3
2
1

我认为这就是您正在寻找的。

我不是 MySQL 专家，但我还发现 LIMIT 的一些巧妙功能，允许您指定返回结果集的上限和下限，因此如果您确切知道表中预期有多少行(您可能不会，但我认为这可能值得一提以利于您的理解)，您可能可以这样(仅 MySQL 示例:)

SELECT * FROM Test ORDER BY Number DESC LIMIT 6, 10

我会按照我在这里提供的示例来尝试修复您的查询，但由于它的格式有点糟糕，所以很难说出到底发生了什么......:

SELECT * FROM (SELECT Research.Focus, Research.Media, Country.Name, GROUP_CONCAT(DISTINCT AskMethod.Name ORDER BY ResearchAskMethod.MethodID SEPARATOR ', ') as AskMethodName, Research.ResearchDate, Research.ResearchID FROM AskMethod INNER JOIN ((Country INNER JOIN Research ON Country.CountryID = Research.CountryID) INNER JOIN ResearchAskMethod ON Research.ResearchID = ResearchAskMethod.ResearchID) ON AskMethod.MethodID = ResearchAskMethod.MethodID WHERE Research.ResearchID=ResearchAskMethod.ResearchID AND Research.ResearchDate=1996 GROUP BY Research.ResearchID ORDER BY Country.Name, Research.Media, AskMethodName, Research.ResearchDate) AS Result ORDER BY Country.Name, Research.Media, AskMethodName, Research.ResearchDate DESC ;

为了记录在案，我建议稍微清理一下查询的布局，以便它们更容易理解。使用断行、空格等，就像在 PHP、C#、Java、C++ 或任何其他编程语言中一样，以提高代码的可读性。