您 可以 做您想做的事，但是我不确定 为什么
要这么做。获得动态列别名后，如何计划引用它们？也就是说，如果您从数据库中提取列别名，那么您将如何使用它们呢？我可能错过了您提出问题的原因。
无论如何，我假设您具有这样的结构：
CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `username` varchar(255) default NULL, PRIMARY KEY (`id`));CREATE TABLE `role` ( `id` int(11) NOT NULL auto_increment, `role` varchar(255) default NULL, PRIMARY KEY (`id`));CREATE TABLE `user_role` ( `user_id` int(11), `role_id` int(11), PRIMARY KEY (`user_id`, `role_id`));INSERT INTO `user` (`username`) VALUES ('Bob'), ('Alice'), ('Carol'), ('Dave'), ('Eve');INSERT INTO `role` (`role`) VALUES ('Super'), ('Admin'), ('View'), ('User'), ('Email');INSERT INTO `user_role` VALUES (1,1), (2,2), (3,3), (4,4), (5,5);由此，您可以获得有关用户及其角色的信息：
SELECt username, role.id AS role_id, role.role AS role FROM user_roleJOIN user ON user.id = user_role.user_idJOIN role ON role.id = user_role.role_id;+----------+---------+-------+| username | role_id | role |+----------+---------+-------+| Bob | 1 | Super || Alice | 2 | Admin || Carol | 3 | View || Dave | 4 | User || Eve | 5 | Email |+----------+---------+-------+
您还可以为特定角色创建列别名：
SELECt username, (role.id = 1) AS Super FROM user_roleJOIN user ON user.id = user_role.user_idJOIN role ON role.id = user_role.role_id;+----------+-------+| username | Super |+----------+-------+| Bob | 1 || Alice | 0 || Carol | 0 || Dave | 0 || Eve | 0 |+----------+-------+
但是，如果我正确理解了您的问题，那么您想要做的就是根据角色名称生成列别名。您不能在MySQL语句中使用变量作为列别名，但是可以构造一个准备好的语句：
SET @sql = (SELECt CONCAt( 'SELECT username, ', GROUP_CONCAt('(role.id = ', id, ') AS ', role SEPARATOR ', '), ' FROM user_role ', 'JOIN user ON user.id = user_role.user_id ', 'JOIN role ON role.id = user_role.role_id;')FROM role);SELECt @sql;+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| @sql |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| SELECT username, (role.id = 1) AS Super, (role.id = 2) AS Admin, (role.id = 3) AS View, (role.id = 4) AS User, (role.id = 5) AS Email FROM user_role JOIN user ON user.id = user_role.user_id JOIN role ON role.id = user_role.role_id; |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+如您从输出中所见，它将生成一个包含SQL SELECt语句的字符串。现在，您需要从该字符串创建一个准备好的语句，并执行结果：
PREPARE stmt FROM @sql;EXECUTE stmt;+----------+-------+-------+------+------+-------+| username | Super | Admin | View | User | Email |+----------+-------+-------+------+------+-------+| Bob | 1 | 0 | 0 | 0 | 0 || Alice | 0 | 1 | 0 | 0 | 0 || Carol | 0 | 0 | 1 | 0 | 0 || Dave | 0 | 0 | 0 | 1 | 0 || Eve | 0 | 0 | 0 | 0 | 1 |+----------+-------+-------+------+------+-------+
编辑
为了使调用交叉表查询更容易，您可以将整个过程包装在存储过程中。在以下示例中，如上所述，我无法
GROUP_CONCAT在
SET@sql语句内工作。相反，我不得不将其分成自己的变量。我不确定为什么这行不通，但是最终结果是相同的，并且代码的神秘性可能有所降低：
DELIMITER //DROp PROCEDURE IF EXISTS test.crosstab//CREATE PROCEDURE test.crosstab()BEGIN SET @cols = (SELECT GROUP_CONCAt( '(role.id = ', id, ') AS ', role SEPARATOR ', ') FROM role); SET @sql = CONCAt( 'SELECt username, ', @cols, ' FROM user_role ', 'JOIN user ON user.id = user_role.user_id ', 'JOIN role ON role.id = user_role.role_id;'); PREPARE stmt FROM @sql; EXECUTE stmt;END;//DELIMITER ;CALL test.crosstab();+----------+-------+-------+------+------+-------+| username | Super | Admin | View | User | Email |+----------+-------+-------+------+------+-------+| Bob | 1 | 0 | 0 | 0 | 0 || Alice | 0 | 1 | 0 | 0 | 0 || Carol | 0 | 0 | 1 | 0 | 0 || Dave | 0 | 0 | 0 | 1 | 0 || Eve | 0 | 0 | 0 | 0 | 1 |+----------+-------+-------+------+------+-------+
