mysql - 高级mysql分组问题-脑筋急转弯

Question

在过去的一年里，我一直在大量使用 stackoverflow - 一个优秀的来源和伟大的贡献者。现在是我请求帮助的时候了。

设置正常:订单、OrderArticles 和文章

我想获得去年销售的商品总量，但仅限于最好的 5 周。

别管 WEEK 函数和 UNIXTIME 等等 - 我已经知道了。我的问题是是否可以不求助于存储过程或函数。

我为每周和文章的摘要创建了一个子查询，并按总和降序排列结果。现在 - 我只需要将查询限制为 5。简单，但我还必须过滤 ArticleID 上的结果但是因为我在子查询中我无法访问外部 ArticleID 并且它无济于事加入结果 - 为时已晚;-)

语法(没有实际的 sql 很难理解，对吧...？)

SELECT a.ID, [more fields], omg.total
FROM Articles AS a
LEFT JOIN
(
    SELECT weeklytotals.articleID, weeklytotals.total
    FROM
    (
        SELECT SUM(ra.quantity) AS total, ra.articleID AS articleID
        FROM OrderArticles ra
        INNER JOIN Orders r
            ON ra.orderID = r.ID
        WHERE r.timeCreated >= UNIX_TIMESTAMP('2011-06-30')
        GROUP BY ra.articleID, WEEK(FROM_UNIXTIME(r.timeCreated))
        ORDER BY SUM(ra.quantity) DESC
    ) AS weeklytotals
    WHERE omg.articleID = a.ID --<-- THIS IS NOT WORKING BUT NECESSARY!
    LIMIT 0, 5
) AS omg
ON omg.articleID = a.ID
WHERE a.isEnabled = 1 --more WHERE-thingys 

此处返回前 5 篇文章并将它们与正确的文章联系起来。是的。

我省略了 SUM 函数(它可以进入 omg-SELECT)。

你明白了吗？我明白我想要什么吗？是的，我们当然知道!

提前致谢。

编辑:条件已经改变 - 这让我的生活更轻松，但我仍然想知道是否有解决问题的办法。

Answer 1

如果您要求 omg 子查询使用 a 表中的数据，请将其放入 SELECT 部分而不是 FROM 部分。使用 mysql 文档中的术语，您需要 correlated subquery 的结果显示为 scalar operand在外部结果集中。

尽管您在示例查询中遗漏了 SUM，但您曾写过对总和感兴趣，即每篇文章只有一个数字。我的方法依赖于那个总和，如果你真的需要最好的五个星期中的每一周都有不同的值，那么我的方法可能会以糟糕的方式崩溃。

SELECT a.ID, [more fields], IFNULL(SUM(
    (
        SELECT SUM(ra.quantity) AS total
        FROM OrderArticles ra
        INNER JOIN Orders r
           ON ra.orderID = r.ID
        WHERE ra.articleID = a.ID -- <-- reference a.ID here
          AND r.timeCreated >= UNIX_TIMESTAMP('2011-06-30')
        GROUP BY WEEK(FROM_UNIXTIME(r.timeCreated))
        ORDER BY SUM(ra.quantity) DESC
        LIMIT 0, 5
    )), 0) AS total
FROM Articles AS a
WHERE a.isEnabled = 1 --more WHERE-thingys 
GROUP BY a.ID

我在这里不是在谈论性能。以这种方式放置子查询，它将对结果集的每一行执行。因此，如果您有大量文章，实际使用起来可能会太慢。但如果发生这种情况，我怀疑存储过程或类似技巧是否会更好。

编辑: 我发现我最初的建议是使用嵌套两层的子查询，不允许访问最里面的子查询以使用最外层的列。但是玩弄 this on sqlfiddle我还发现可以安全地将子查询的结果传递给 sum，从而避免一层嵌套。所以上面的代码实际上已经被 MySQL 服务器检查和执行，因此应该按预期工作。