mysql从选择中选择 [英] mysql Select from Select

问题描述

我有这个查询:

    SELECT DATE( a.created_at ) AS order_date, count( * ) as cnt_order
    FROM `sales_order_item` AS a
    WHERE MONTH( a.created_at ) = MONTH( now())-1
    GROUP BY order_date

这将返回类似以下的结果(仅快照，否则将每31天返回一次):

which will return result something like this (snapshot only otherwise will return per 31 days):

order_date  cnt_order 
2012-08-29  580
2012-08-30  839
2012-08-31  1075

我的完整查询正在基于以上选择进行选择:

and my full query is selecting based on above selection:

SELECT order_date
    , MAX(cnt_order) AS highest_order
FROM (
        SELECT DATE (a.created_at) AS order_date
            , count(*) AS cnt_order
        FROM `sales_order_item` AS a
        WHERE MONTH(a.created_at) = MONTH(now()) - 1
        GROUP BY order_date
    ) AS tmax

但结果是:

order_date  highest_order
2012-08-01  1075

哪个日期有误，并且总是在假设2012-08-31的地方选择第一行日期.也许这是我不知道的简单错误.那么如何正确地将日期指向2012-08-31?任何帮助都会很棒.

Which has the date wrong and always pick the first row of date where it suppose 2012-08-31. Maybe this is a simple error that I dont know. So how to get the date right point to 2012-08-31? Any help would be great.

推荐答案

您可以尝试订购子查询结果集；像这样:

You could try ordering the sub query result set; something like:

SELECT
    DATE (a.created_at) AS order_date,
    COUNT(*) AS cnt_order
FROM
    `sales_order_item` AS a
WHERE
    MONTH(a.created_at) = MONTH(now()) - 1
GROUP BY
    order_date
ORDER BY
    cnt_order DESC

这篇关于mysql从选择中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！