问题已修改
真的想要一个 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 中的总列
提前致谢
Change:
group_concat(CAST(quantity AS CHAR))
To
SUM(quantity)
--
SELECT s.`state`, i.`item`, SUM(i.`quantity`) AS quantities
FROM `shops` AS s
LEFT JOIN `items` AS i ON i.`shop` = s.`shopid`
WHERE s.`state` = 5
GROUP BY i.`item`
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
