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

本文介绍了MySQL:动态添加列，与另一个表中的行数一样多的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

运输台

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:

查询以发现不同的货盘ID.

使用应用程序代码构建动态SQL查询，该查询的列数与在第一个查询中找到的不同托盘ID值一样多.

运行生成的动态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.