按月分组并保留产品名称

I'm not sure if it's possible just with one query, but here is my problem:

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);

The returned result is:

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
        )

)

Since everything is grouped by month, all the names of the products are the same.

Is there any way around this? I need this to render a jQuery Flot chart, so it needs to be grouped by month. But I want to use product titles as chart labels.

dqeonr8554
dqeonr8554 那么,您是否真的需要每种不同产品的月销售数量?如果是这种情况,您应该只能将产品添加到您的组中。
大约 5 年之前 回复
dongliyun3301
dongliyun3301 你是绝对正确的,交配。如果我们删除JOIN,GROUP_CONCAT将执行该操作,并在SELECT语句中放置一个子查询。
大约 5 年之前 回复
douyou2234
douyou2234 如果OP只想要一串标题,那可能会有用。但OP正在将产品表加入按月分组的付款表。因此每个月只加入一种产品。我不认为GROUP_CONCAT可以在不重组查询的情况下工作。此外,我不清楚OP打算如何在显示聚合数据的Flot图表中使用产品标题。
大约 5 年之前 回复
duanfan8699
duanfan8699 嗯,如何使用GROUP_CONCAT加入同一领域的所有产品名称?
大约 5 年之前 回复
dqsong2010
dqsong2010 按月对产品进行分组会阻止您单独有效地列出产品。我建议执行两个查询:一个用于获取所有产品,另一个用于获取每月数字。或者,获取所有产品并使用PHP计算该结果的每月数字。
大约 5 年之前 回复
duankun9280
duankun9280 我现在只有4种产品。我们的想法是列出所有产品以及每个月的总销售额,销售数量等。
大约 5 年之前 回复
doubi2014
doubi2014 我明白了,好的。如果一个月内有多个产品分组,您会显示哪些产品的标题?
大约 5 年之前 回复

1个回答



如果您想按月分组,但有一列列出所有产品标题,您可以像这样使用GROUP_CONCAT </ p>

  SELECT 
MONTH(p.created)AS月,
GROUP_CONCAT(DISTINCT(pd.title)SEPARATOR',')AS产品,
SUM(p.amount)AS销售额 ,
AVG(p.amount)AS avg,
COUNT(p.product_id)AS总计
FROM
付款AS p
LEFT JOIN产品AS pd
ON pd.id = p.product_id
WHERE p.status = 1
GROUP BY MONTH(p.created);
</ code> </ pre>

这将给你一个像这样的结果</ p>

  | 月| 产品| 销售| 平均| 总计| 
| ------- | --------------------------------- | --- ---- | -------- | ------- |
| 3 | 产品2,产品3,产品1 | 10 | 2.5 | 4 |
| 4 | 产品4,产品1 | 7 | 2.3333 | 3 |
| 5 | 产品2,产品4,产品1 | 10 | 2.5 | 4 |
| 6 | 产品2,产品3 | 6 | 2 | 3 |
</ code> </ pre>

http:// sqlfiddle.com/#!9/a52a66/5 </ p>
</ div>

展开原文

原文

If you want to group by month but have a column that list all the products title, you could use GROUP_CONCAT like this

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);

This will give you a result like this

| 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

douyuben9434
douyuben9434 它有点工作,但不完全是我想要的。
大约 5 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐