首页 / MYSQL / 交叉表的动态MySQL查询/视图
交叉表的动态MySQL查询/视图
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了交叉表的动态MySQL查询/视图,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5432字,纯文字阅读大概需要8分钟。
内容图文
我目前有一个带有以下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;
我的问题是,是否可以从角色表中的记录动态生成角色列.
解决方法:
你可以做你想做的事,但我不确定你为什么要这样做.获得动态列别名后,您如何计划引用它们?也就是说,如果从数据库中提取列别名,那么您将如何使用它们呢?我可能错过了你的问题背后的原因.
无论如何,我假设你有这样的结构:
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 |
+----------+-------+-------+------+------+-------+
内容总结
以上是互联网集市为您收集整理的交叉表的动态MySQL查询/视图全部内容,希望文章能够帮你解决交叉表的动态MySQL查询/视图所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。