动态MySQL查询/查看交叉表

本文介绍了动态MySQL查询/查看交叉表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我目前有一个硬编码的视图与以下sql：

 选择用户名
，（case user_role。如果ROLE_SUPER 
，（当user_role.role_id为2，则返回true，否则返回false）结束为ROLE_ADMIN 
，（如果user_role.role_id为3则返回true，否则为false结束） ROLE_VIEW 
，（case 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 
在user.id = user_role.user_id 
上留下外连接user_role在user_role.role_id = role.id上留下外连接角色;

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

解决方案

您可以完成您想要做的事情，但我不确定为什么你想要。一旦你有你的动态列别名，你如何计划引用它们？也就是说，如果您从数据库中提取列别名，那么您将如何使用它们？

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

<$ （
`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 ）;

插入`role`（`role`）VALUES
（'Super'），（'Admin'），（'View'），（'User'），（'Email ）;

INSERT INTO`user_role` VALUES
（1,1），（2,2），（3,3），（4,4），（5,5）;

从中可以获得关于用户及其角色的信息： b
$ b
SELECT用户名，role.id AS role_id，role.role AS角色FROM user_role JOIN用户ON user.id = user_role.user_id 加入角色role.id = user_role.role_id; + ---------- + --------- + ------- + |用户名| role_id |角色| + ---------- + --------- + ------- + | Bob | 1 |超级| | Alice | 2 |管理员| |卡罗尔| 3 |查看| |戴夫| 4 |用户| | Eve | 5 |电子邮件| + ---------- + --------- + ------- +
您还可以为特定角色创建列别名：

SELECT username，（role.id = 1）AS Super FROM user_role JOIN用户ON user.id = user_role.user_id JOIN角色ON role.id = user_role.role_id; + ---------- + ------- + |用户名|超级| + ---------- + ------- + | Bob | 1 | | Alice | 0 | |卡罗尔| 0 | |戴夫| 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 | + --------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------- + |选择用户名，（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角色ON role.id = user_role.role_id; | + --------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------- +
正如您将从输出中看到的那样，它会生成一个包含SQL SELECT语句的字符串。您现在需要从该字符串中创建一个准备好的语句，然后执行结果：

准备好@sql; EXECUTE stmt; + ---------- + ------- + ------- + ------ + ------ + ---- --- + |用户名|超级|管理员|查看|用户|电子邮件| + ---------- + ------- + ------- + ------ + ------ + ---- --- + | Bob | 1 | 0 | 0 | 0 | 0 | | Alice | 0 | 1 | 0 | 0 | 0 | |卡罗尔| 0 | 0 | 1 | 0 | 0 | |戴夫| 0 | 0 | 0 | 1 | 0 | | Eve | 0 | 0 | 0 | 0 | 1 | + ---------- + ------- + ------- + ------ + ------ + ---- --- +
编辑

为了更轻松地调用交叉表查询，可以将整个事件包装在存储过程中。在下面的例子中，我无法让 GROUP_CONCAT 在 SET @sql 语句中工作，如上所述。相反，我必须把它分解成它自己的变量。我不确定为什么这不起作用，但最终结果是一样的，而且代码可能不那么神秘：

DELIMITER // DROP PROCEDURE如果存在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'， '加入用户ON user.id = user_role。 user_id'， 'JOIN角色ON role.id = user_role.role_id;'）; PREPARE stmt FROM @sql; EXECUTE stmt; END; // DELIMITER; CALL test.crosstab（）; + ---------- + ------- + ------- + ------ + ------ + ------- + |用户名|超级|管理员|查看|用户|电子邮件| + ---------- + ------- + ------- + ------ + ------ + ---- --- + | Bob | 1 | 0 | 0 | 0 | 0 | | Alice | 0 | 1 | 0 | 0 | 0 | |卡罗尔| 0 | 0 | 1 | 0 | 0 | |戴夫| 0 | 0 | 0 | 1 | 0 | | Eve | 0 | 0 | 0 | 0 | 1 | + ---------- + ------- + ------- + ------ + ------ + ---- --- +

I currently have a hard coded view with the following sql:
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;
my question is whether or not it is possible to dynamically generate role columns from the records in the role table.
解决方案
You can do what you want to do, but I am not sure why you would want to. Once you have your dynamic column aliases, how do you plan on referencing them? That is, if you pull your column aliases from the database, how will you then be able to use them? I may be missing the reason behind your question.
Anyway, I assume you have a structure like this:
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);
From that, you can obtain information about users and their role(s):
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 | +----------+---------+-------+
You can also create a column alias for a specific role:
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 | +----------+-------+
However, if I understand your question correctly, what you want to do is to generate the column alias from the role name. You cannot use a variable as a column alias in a MySQL statement, but you can construct a prepared statement:
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; | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
As you will see from the output, that generates a string which contains a SQL SELECT statement. You now need to create a prepared statement from that string, and execute the result:
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 | +----------+-------+-------+------+------+-------+
EDIT
To make calling the crosstab query easier, you could wrap the whole thing up in a stored procedure. In the following example, I could not get the GROUP_CONCAT to work within the SET @sql statement, as it does above. Instead, I had to separate it off into its own variable. I'm not sure why this didn't work, but the end result is the same, and the code is perhaps a little less cryptic:
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 | +----------+-------+-------+------+------+-------+

这篇关于动态MySQL查询/查看交叉表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！