时间戳

首页 > 解决方案 > 如何使用存储过程或数据透视表选择来操作我的数据库数据？

mysql - 如何使用存储过程或数据透视表选择来操作我的数据库数据？

问题描述

我的数据库中有三个表。用户、应用程序和应用程序访问表，该表将应用程序与用户配对以表示用户对所述应用程序的访问。

Create table applications (
    id int,
    app_name varchar(45) not null unique,
    app_link varchar(70),
    description varchar(400),
    primary key (id)
);

create table users (
    id int,
    windows_id varchar(25) not null unique,
    full_name varchar(50),
    user_role int,
    email char(50),
    is_active int,
    primary key (id)
);

create table app_access (
    user_id int,
    app_id int,
    app_name varchar(30),
    primary key(user_id, app_id),
    constraint app_fk foreign key (app_id)
    references applications(id),
    constraint user_fk foreign key (user_id)
    references users(id)
);

我希望能够创建一个 select 语句，它将从我当前的表中生成一个如下所示的表：

当前表：

在此处输入图像描述

在上面的数据中，john 可以访问这两个应用程序，而 bob 只能访问 byewrld。

我想从 sql select 语句中得到这样的表：

在此处输入图像描述

我可以在我的表格设计中硬编码这些值（每个应用程序作为一列），但应用程序和用户的列表正在增长，我希望能够看到用户对应用程序的访问，如上所示。

标签： mysqlstored-procedurespivot

解决方案

试试这个，所以这将显示基于新应用程序创建的顺序

delimiter $$

create procedure sp_app_user()
begin

select group_concat(concat("sum(if(aa.app_name ='",app_name,"',1,0)) '",app_name,"'")) into @pivotcol
from (select app_name from applications group by app_name order by id) t;

set @pivotquery =concat('select u.id, ',@pivotcol,' from app_access aa 
join users u on aa.user_id=u.id
join applications a on aa.app_id=a.id
group by u.id;');

prepare stmt from @pivotquery;
execute stmt;
deallocate prepare stmt;

end $$
delimiter ;

推荐阅读