运输台
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:
对于所有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:动态添加列,与另一个表中的行数一样多
发布评论