MySQL:动态添加列，与另一个表中的行数一样多 [英] MySql: adding columns dynamically, as many as rows in another table

问题描述

运输台

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). 通知后，另一位经理可以填写运输托盘容量"表.

Problem: pallets and transports can be added, so, neither quantity is known in advance. For example, manager adds another pallet type and 'pallet_id_3_capacity' column should be generated (and can show null if no capacity data is yet available). Another manager can fill 'transport pallet capacity' table later when notified.

有没有一种方法可以在mysql中构建sql来解决上述问题:特别是-托盘的动态数量?

Is there a way to build sql in mysql that will care about the above: specifically - dynamic number of pallets?

推荐答案

编写查询时必须固定SQL选择列表.您不能使SQL能够根据找到的数据自动扩展其列.

The SQL select-list must be fixed at the time you write the query. You can't make SQL that auto-expands its columns based on the data it finds.

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

But your request is common, it's called a pivot-table or a crosstab table.

唯一的解决方案是分多个步骤进行:

The only solution is to do this in multiple steps:

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

对于所有SQL数据库(不仅是MySQL)都是如此.

This is true for all SQL databases, not just MySQL.

请参见 MySQL数据透视表行转换为动态列数用于在MySQL中产生数据透视表查询的投票解决方案.

See MySQL pivot row into dynamic number of columns for a highly-voted solution for producing a pivot-table query in MySQL.

我没有将您的问题作为该问题的重复进行投票，因为您的查询还涉及transport_id，这将使查询解决方案有所不同.但是，了解其他数据透视表解决方案应该可以帮助您入门.

I am not voting your question as a duplicate of that question, because your query also involves transport_id, which will make the query solution a bit different. But reading about other pivot-table solutions should get you started.

这篇关于MySQL:动态添加列，与另一个表中的行数一样多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！