主页 / dba / 问题 / 188177
Accepted
mal
Asked: 2017-10-11 23:29:16 +0800 CST

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

  • 772

我在一个 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 |

这样的事情可能吗？还是我最好一开始就以这种方式创建表格。
最初的想法是将来添加新类型应该很容易。

mysql join

1 个回答

  1. Best Answer

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

    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和聚合也可以改善性能，这取决于有多少属性、查询中还有哪些其他过滤子句以及表的索引方式。在许多情况下它会更好，但在某些情况下它会明显更糟（特别是在过滤其中一个转置属性或从每个项目可能包含许多属性的包中挑选一些属性时）由于导致扫描其中少量seeks 否则可能就足够了：

    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/ 等价物的数据库中，情况通常也是如此，因为此类功能通常是在考虑固定属性/列的情况下实现的。

    • 4

相关问题