如何在group_concat()中使用sum()? [英] How to use sum() within a group_concat()?

问题描述

问题已修改

表:商店

+---------+--------+--------+
| shop_id | name   | state  |
+---------+--------+--------+
|    0    | shop 0 |    5   |
|    1    | shop 1 |    5   |
|    2    | shop 2 |    5   |
|    3    | shop 3 |    2   |
+---------+--------+--------+

表:项目

+------------+--------------+
|   shop  | item | quantity | 
+------------+--------------+
|    0    |  0   |    1     |
|    0    |  1   |    2     |
|    0    |  2   |    3     |
|    1    |  0   |    1     |
|    1    |  1   |    2     |
|    1    |  2   |    3     |
|    2    |  0   |    1     |
|    2    |  1   |    2     |
|    2    |  2   |    3     |
|    3    |  0   |    1     |
|    3    |  1   |    2     |
|    3    |  2   |    3     |
+------------+--------------+

    SELECT state,SUM(i.quantity) total
    FROM shops s2
    LEFT JOIN items i ON i.shop=s2.shopid
    WHERE state=5
    GROUP by item

result #1:

+--------+---------+
| state  |  total  |
+--------+---------+
|    5   |    3    |
+--------+---------+
|    5   |    6    |
+--------+---------+
|    5   |    9    |
+--------+---------+

But I would like the totals, like this:
result #2:
+--------+---------+---------+----------+
| state  | total 0 | total 1 |  total 2 |
+--------+---------+---------+----------+
|    5   |    3    |     6   |    9     |
+--------+---------+---------+----------+

or using group_concat()
result #3

+--------+---------+
| state  | totals  |
+--------+---------+
|    5   |  3,6,9  |
+--------+---------+

我似乎无法让group_concat抢占结果#1中的总列

I cannot seem to get group_concat to grab the total column in result #1

预先感谢

推荐答案

找到了一种方法:

SELECT state,GROUP_CONCAT(cast(total as char))
FROM
(
    SELECT state,SUM(i.quantity) total
    FROM shops s
    LEFT JOIN items i ON i.shop=s.shopid
    WHERE state=5
    GROUP by item
) s

这篇关于如何在group_concat()中使用sum()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！