php - 按月查询结果

Question

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

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

我不想重复“国家/地区”。有什么办法可以做到吗？

Answer 1

目前尚不清楚您期望的输出是什么。但您可以从 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