mysql - 如何使用 codeigniter 事件记录模式将行转换为列？

Question

这是我的 table

+------+------+-------------+
| sid  | cid  | amount      |
+------+------+-------------+
| 32   | 1    | 500         |
+------+------+-------------+
| 33   | 1    | 300         |
+------+------+-------------+
| 32   | 2    | 500         |
+------+------+-------------+
| 33   | 2    | 400         |
+------+------+-------------+
| ..   | .    | ...         |
+------+------+-------------+
| ..   | .    | ...         |
+------+------+-------------+
| 32   | n    | 100         |
+------+------+-------------+
| 33   | n    | 100         |
+------+------+-------------+

在我看来，我想在我的 View 中显示它，例如

+------+----------+-----------+------+------------+------------+
| sid  | amount(1)| amount(2) |  ... | amount(n)  |Total amount|
+------+----------+-----------+------+------------+------------+
| 32   | 500      | 500       |  ... | 100        |1100        |
+------+----------+-----------+------+------------+------------+
| 33   | 300      | 400       |  ... | 100        |800         |
+------+----------+-----------+------+------------+------------+

我该怎么做？

Answer 1

您可以使用 SQL 的 GROUP_CONCAT、SUM 和 GROUP 子句。但是，不会有多个金额列。 sid 的金额列表将全部位于一列中，值之间用逗号分隔。

SELECT sid,
       GROUP_CONCAT(amount) AS amount,
       SUM(amount) AS total
FROM mytbl
GROUP BY sid;