mysql - 交叉表的动态 MySQL 查询/ View

Question

我目前有一个带有以下 sql 的硬编码 View :

select username
    ,(case user_role.role_id when 1  then true else false end) as ROLE_SUPER 
    ,(case user_role.role_id when 2  then true else false end) as ROLE_ADMIN
    ,(case user_role.role_id when 3  then true else false end) as ROLE_VIEW
    ,(case user_role.role_id when 4  then true else false end) as ROLE_USER
    ,(case user_role.role_id when 5  then true else false end) as ROLE_EMAIL
    from user 
    left outer join user_role on user.id=user_role.user_id
    left outer join role on user_role.role_id = role.id;

我的问题是是否可以从角色表中的记录动态生成角色列。

Answer 1

您可以做您想做的事，但我不确定为什么您会想要这样做。有了动态列别名后，您打算如何引用它们？也就是说，如果您从数据库中提取您的列别名，您将如何使用它们？我可能遗漏了您提出问题的原因。

无论如何，我假设你有这样的结构:

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