php - 按月分组并保留产品标题

我不确定是否只有一个查询是可能的，但这是我的问题：

SELECT
  SUM(p.amount) AS sales,
  AVG(p.amount) AS avg,
  COUNT(p.product_id) AS total,
  pd.title
FROM
  payments AS p
  LEFT JOIN products AS pd
    ON pd.id = p.product_id
WHERE p.status = 1
GROUP BY MONTH(p.created);

返回的结果是：

Array
(
    [0] => stdClass Object
        (
            [sales] => 979.90
            [avg] => 97.990000
            [total] => 10
            [title] => Product 1
        )

    [1] => stdClass Object
        (
            [sales] => 1139.84
            [avg] => 71.240000
            [total] => 16
            [title] => Product 1
        )

    [2] => stdClass Object
        (
            [sales] => 789.89
            [avg] => 71.808182
            [total] => 11
            [title] => Product 1
        )

    [3] => stdClass Object
        (
            [sales] => 739.87
            [avg] => 56.913077
            [total] => 13
            [title] => Product 1
        )

    [4] => stdClass Object
        (
            [sales] => 569.85
            [avg] => 37.990000
            [total] => 15
            [title] => Product 1
        )

    [5] => stdClass Object
        (
            [sales] => 999.78
            [avg] => 45.444545
            [total] => 22
            [title] => Product 1
        )

    [6] => stdClass Object
        (
            [sales] => 569.91
            [avg] => 63.323333
            [total] => 9
            [title] => Product 1
        )

    [7] => stdClass Object
        (
            [sales] => 199.96
            [avg] => 49.990000
            [total] => 4
            [title] => Product 1
        )

)

由于所有内容均按月分组，因此所有产品名称均相同。

有没有办法解决？我需要它来呈现jQuery Flot chart，因此需要按月分组。但是我想使用产品标题作为图表标签。

最佳答案

如果您想按月分组，但有一个列列出所有产品标题的列，则可以使用GROUP_CONCAT这样

SELECT
  MONTH(p.created) AS month,
  GROUP_CONCAT(DISTINCT(pd.title) SEPARATOR ', ') AS products,
  SUM(p.amount) AS sales,
  AVG(p.amount) AS avg,
  COUNT(p.product_id) AS total
FROM
  payments AS p
LEFT JOIN products AS pd
    ON pd.id = p.product_id
WHERE p.status = 1
GROUP BY MONTH(p.created);

这会给你这样的结果

| month |                        products | sales |    avg | total |
|-------|---------------------------------|-------|--------|-------|
|     3 | product 2, product 3, product 1 |    10 |    2.5 |     4 |
|     4 |            product 4, product 1 |     7 | 2.3333 |     3 |
|     5 | product 2, product 4, product 1 |    10 |    2.5 |     4 |
|     6 |            product 2, product 3 |     6 |      2 |     3 |

http://sqlfiddle.com/#!9/a52a66/5