PostgreSQL查询数据(连接查询和子查询)

原料

--用户表
create table "SysUser"(
    "UserId" serial,                  --用户Id，自增
    "UserName" character varying(50), --用户名
    "Pwd" character varying(50),      --密码
    "Status" smallint,                --状态 
    primary key("UserId")             --设置UserId主键
)
--角色表
create table "SysRole"(
    "RoleId" serial,                  --角色Id
    "RoleName" character varying(50), --角色名称
    primary key("RoleId") 
)
--用户角色关系表
create table "SysUserRole"(
      "UserId" integer,--用户Id              
        "RoleId" integer,--角色Id
    primary key("UserId","RoleId")
)

--测试数据
insert into "SysUser" ("UserName","Pwd") values ('username1','123456');
insert into "SysUser" ("UserName","Pwd") values ('username2','123456');
insert into "SysUser" ("UserName","Pwd") values ('username3','123456');
insert into "SysUser" ("UserName","Pwd") values ('username4','123456');
insert into "SysRole" ("RoleName") values ('Role1');
insert into "SysRole" ("RoleName") values ('Role2');
insert into "SysUserRole" ("UserId","RoleId") values (1,1);
insert into "SysUserRole" ("UserId","RoleId") values (2,2);
insert into "SysUserRole" ("UserId","RoleId") values (3,1);
insert into "SysUserRole" ("UserId","RoleId") values (3,2);

SysUser

SysRole

SysUserRole

1.查询某一用户拥有的所有角色

select * from "SysRole" where "RoleId" in (
    select "RoleId" from "SysUserRole" where "UserId"=3
)

数据输出

2.查询某一角色下的所有用户

select * from "SysUser" where "UserId" in(
    select "UserId" from "SysUserRole" where "RoleId"=1
)

数据输出

3.查询所有的用户角色信息

select u."UserName",r."RoleName" from "SysUserRole" m
left join "SysUser" u on m."UserId"=u."UserId"
left join "SysRole" r on m."RoleId"=r."RoleId"
order by u."UserName"

数据输出