mysql 动态视图_动态MySQL查询/视图的交叉表

小编典典

您 可以 做您想做的事，但是我不确定 为什么

要这么做。获得动态列别名后，如何计划引用它们？也就是说，如果您从数据库中提取列别名，那么您将如何使用它们呢？我可能错过了您提出问题的原因。

无论如何，我假设您具有这样的结构：

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_role

JOIN user ON user.id = user_role.user_id

JOIN 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_role

JOIN user ON user.id = user_role.user_id

JOIN 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 |

+----------+-------+-------+------+------+-------+

2020-06-20