mysql – 是否可以重命名结果集的列？

2023-03-16 14:11:38

我在ID上加了两个表.

他们可能看起来像这样：

table_overview  
| id | version | description |
------------------------------
| 1  | 1.2.3   | new stuff.. |


table_files
| id | type    | file_name   |
------------------------------
| 1  | TEXT    | as.txt      |
| 1  | IMG     | abc.png     |
| 1  | AUDIO   | def.wav     |

table_overview的主键是id,table_files的主键是id和type.

我想返回一个结果集,其中每行包含所有信息,但改为使用类型替换标题file_name,因此结果集中的行看起来像这样：

| id | version | description | text   | img     | audio   |
| 1  | 1.2.3   | new stuff.. | as.txt | abc.png | def.wav |

这样的事情可能吗？或者我最好只是简单地创建表格.
最初的想法是,将来添加新类型应该很容易.

解决方法:

如果您有一组固定的属性,并且每个实体最多只有一个值,那么您可以执行以下操作：

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
LEFT OUTER JOIN 
       table_files AS t ON t.id=o.id AND t.type = 'TEXT'
LEFT OUTER JOIN 
       table_files AS i ON i.id=o.id AND i.type = 'IMG'
LEFT OUTER JOIN 
       table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

或者如果每个属性总是有一行(即如果没有值,则该行将以NULL值存在,而不是根本没有该属性的行)对于使用内连接的每个实体可能更有效：

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
JOIN   table_files AS t ON t.id=o.id AND t.type = 'TEXT'
JOIN   table_files AS i ON i.id=o.id AND i.type = 'IMG'
JOIN   table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

如果任何给定实体可能存在多个属性(例如,如果一个概览行可能有多个图像文件),那么这将变得更加复杂.

正如Raymond所提到的,使用GROUP BY和聚合可以更好地改变性能,具体取决于有多少属性,查询中的其他过滤子句以及表的索引方式.在许多情况下它会更好,但在某些情况下它会明显更糟(特别是当过滤其中一个转置属性或从一个可以包含许多项目的包中挑选一些属性时)由于导致少量的扫描寻求可能就足够了：

SELECT o.id, o.version, o.description
     , MAX(IF(type = 'TEXT' , file_name, NULL)) AS text
     , MAX(IF(type = 'IMG'  , file_name, NULL)) AS img
     , MAX(IF(type = 'AUDIO', file_name, NULL)) AS audio
FROM   table_overview AS o
LEFT OUTER JOIN
       table_files AS f ON f.id=o.id
GROUP BY o.id, o.version, o.description

您需要对应用程序进行测试/基准测试,以确定此方法是否更快.

在像SQL Server这样的其他数据库中,有一些PIVOT功能可以减轻这种麻烦,但IIRC mySQL还没有这样的功能.

如果您没有需要转置的固定属性集,那么您将*使用由应用程序层或存储过程生成的临时SQL(有关后者的一个示例,请参阅http://buysql.com/mysql/14-how-to-automate-pivot-tables.html).即使在具有PIVOT / UNPIVOT / equivelant的DB中也是如此,因为这些特征通常在考虑固定属性/列的情况下实现.

码农公寓

相关文章