结果查询基于每月 - Result query based on every month

Result query based on every month

本文关键字：于每月查询结果 | 更新日期: 2023-09-27

我有一个查询，可以根据每个月和国家/地区获取购买的物品数量。

SELECT   COUNT( `country` ) as item,
         `country`,
         MONTH(purchase_date) AS Month,
         YEAR( purchase_date) AS year
FROM     charmy_purchase
WHERE    YEAR(purchase_date)='20112'
GROUP BY country,MONTH(purchase_date)
ORDER BY country ASC

结果我明白了。

 item   country  Month  year
    2   India       5   2012
    1   India       6   2012
    1   Malasiya    5   2012
    3   Singapure   1   2012
    1   Singapure   4   2012

我不想重复"笨拙"。有什么办法可以做到吗。

目前尚不清楚您期望的输出。但是您可以从GROUP BY country,MONTH(purchase_date)中删除MONTH(purchase_date)：

SELECT   COUNT( `country` ) as item,
     `country`,
     MONTH(purchase_date) AS Month,
     YEAR( purchase_date) AS year
FROM     charmy_purchase
WHERE    YEAR(purchase_date)='2012'
GROUP BY country
ORDER BY country ASC

这将为您提供不同的国家/地区。但是，如果一个国家有更多的话，它也会给你任何月份和年份。

编辑

如果您希望月份和年份列在一行逗号中，请尝试以下操作：

SELECT   COUNT( `country` ) as item,
     `country`,
     group_concat(MONTH(purchase_date)) AS Months,
     YEAR( purchase_date) AS year
FROM     charmy_purchase
WHERE    YEAR(purchase_date)='2012'
GROUP BY country
ORDER BY country ASC

请参阅此示例：SQLFiddle

尝试group_concat，如下所示：

SELECT   COUNT( `country` ) as item,
         `country`,
         GROUP_CONCAT(MONTH(purchase_date)) AS Months,
         YEAR( purchase_date) AS year
FROM     charmy_purchase
WHERE    YEAR(purchase_date)='2012'
GROUP BY country
ORDER BY country ASC