3,491
社区成员
发帖
与我相关
我的任务
分享
---项目表
create table Tproject(id number(5),name varchar2(100));
insert into Tproject values(10,'项目一');
insert into Tproject values(11,'项目二');
----用户表
create table Tuser(id number(5),name varchar2(64));
insert into Tuser values('110','甲用户');
insert into Tuser values('112','乙用户');
insert into Tuser values('114','丙用户');
---资质表
create table Tdistinction(id number(3),name varchar2(64));
insert into Tdistinction values(210,'A资质');
insert into Tdistinction values(211,'B资质');
insert into Tdistinction values(212,'C资质');
insert into Tdistinction values(213,'D资质');
insert into Tdistinction values(214,'E资质');
----项目资质关系表
create table ProjectDis(project_id number(5),dis_id number(3),lev number(1));
insert into ProjectDis values(10,210,3);
insert into ProjectDis values(10,212,2);
insert into ProjectDis values(11,211,3);
insert into ProjectDis values(11,212,1);
insert into ProjectDis values(11,213,2);
----用户资质关系表
create table UserDis(user_id number(5),dis_id number(3),lev number(1));
insert into UserDis values(110,210,1);
insert into UserDis values(110,211,2);
insert into UserDis values(110,212,1);
insert into UserDis values(112,210,1);
insert into UserDis values(112,212,3);
insert into UserDis values(114,211,1);
insert into UserDis values(114,212,1);
insert into UserDis values(114,213,1);
insert into UserDis values(114,214,2);
SELECT *
FROM tuser u, tproject p
WHERE (SELECT COUNT (1)
FROM ProjectDis pd INNER JOIN UserDis ud ON pd.dis_id = ud.dis_id
WHERE pd.project_id = p.id
AND ud.user_id = u.id
AND ud.lev <= pd.lev) = (SELECT COUNT (1)
FROM ProjectDis
WHERE project_id = p.id);
SELECT *
FROM tuser u, tproject p
WHERE (SELECT COUNT (1)
FROM ProjectDis pd INNER JOIN UserDis ud ON pd.dis_id = ud.dis_id
WHERE pd.project_id = p.id
AND ud.user_id = u.id
AND ud.lev >= pd.lev) = (SELECT COUNT (1)
FROM ProjectDis
WHERE project_id = p.id);
楼主的示例数据没有符合的项目和用户。如下适当的改了下114用户的用户资质数据,可以返回。
如需查找特定的用户或者项目,在sql最后加一个and条件即可。
insert into UserDis values(114,211,3);
insert into UserDis values(114,212,3);
insert into UserDis values(114,213,3);
insert into UserDis values(114,214,3);