MySql:动态添加列，与另一个表中的行一样多

Question

传输表

id  name
1   T1
2   T2

托盘表

id  name
1   P1
2   P2

运输托盘容量表

id  transport_id  pallet_id  capacity
1   1             1          10
2   1             2          null
3   2             1          20
4   2             2          24

如何生成这样的表:

id  transport_id   pallet_id_1_capacity   pallet_id_2_capacity
1   1              10                     null
2   2              20                     24

问题:可以添加托盘和运输工具，因此，这两个数量都无法提前得知。例如，经理添加了另一种托盘类型，并且应该生成“pallet_id_3_capacity”列(如果尚无可用容量数据，则可以显示 null)。其他经理可以在收到通知后填写“运输托盘容量”表。

有没有一种方法可以在 mysql 中构建 sql 来关心上述内容:特别是 - 动态托盘数量？

Answer 1

SQL 选择列表必须在您编写查询时固定。您无法使 SQL 根据找到的数据自动扩展其列。

但是您的请求很常见，它称为数据透视表或交叉表。

唯一的解决方案是分多个步骤执行此操作:

1. 查询以发现不同的托盘 ID。
2. 使用应用代码构建动态 SQL 查询，其中的列数与第一个查询中找到的不同托盘 ID 值一样多。
3. 运行生成的动态 SQL 查询。

这对于所有 SQL 数据库都是如此，而不仅仅是 MySQL。

参见MySQL pivot row into dynamic number of columns用于在 MySQL 中生成数据透视表查询的高票数解决方案。

我不会将您的问题视为该问题的重复，因为您的查询还涉及transport_id，这将使查询解决方案有点不同。但是阅读其他数据透视表解决方案应该可以帮助您入门。