选择每种类型的用户

发布于 2024-12-11 21:19:10 字数 851 浏览 0 评论 0原文

我有一个针对我试图执行的两个数据库的查询。第一个表只是用户信息，并由权限表引用。对于我的查询，我试图找到一组超级用户，他们是拥有权限表中每个当前权限的用户。它的设置如下：

create table MEMBER
  (  id       int           not null,
     name     varchar(10),
     bdate     date,
     sex       char,
     pc_id     int           not null,
   PRIMARY KEY (mid),
   FOREIGN KEY (pc_id) REFERENCES PLEDGECLASS(id))

create table MEMBER_PRIVILEGE
 ( mid      int            not null,
   pid      int            not null,
   PRIMARY KEY (mid,pid),
   FOREIGN KEY (mid) REFERENCES MEMBER(id),
   FOREIGN KEY (pid) REFERENCES PRIVILEGE(id))

create table PRIVILEGE
( id       int,
  pname    varchar(15)
  PRIMARY KEY(id))

虽然显然是不正确的查询，但我正在尝试执行相当于以下操作的操作：

Select name
From MEMBER,MEMBER_PRIVILEGE
Where id=mid AND pid = ALL (select id
                            From PRIVILEGE);

原文

I have a query against two databases that I'm trying to execute. The first table is just user information and is referenced by a privilege table. For my query I'm trying to find a set of super users, they are users that have every current privilege in the privilege table. It is set up as follows:

create table MEMBER
  (  id       int           not null,
     name     varchar(10),
     bdate     date,
     sex       char,
     pc_id     int           not null,
   PRIMARY KEY (mid),
   FOREIGN KEY (pc_id) REFERENCES PLEDGECLASS(id))

create table MEMBER_PRIVILEGE
 ( mid      int            not null,
   pid      int            not null,
   PRIMARY KEY (mid,pid),
   FOREIGN KEY (mid) REFERENCES MEMBER(id),
   FOREIGN KEY (pid) REFERENCES PRIVILEGE(id))

create table PRIVILEGE
( id       int,
  pname    varchar(15)
  PRIMARY KEY(id))

Although obviously the incorrect query, I'm trying to do something equivalent to the following:

Select name
From MEMBER,MEMBER_PRIVILEGE
Where id=mid AND pid = ALL (select id
                            From PRIVILEGE);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

落在眉间の轻吻 2024-12-18 21:19:10

SELECT *
FROM MEMBER
WHERE NOT EXISTS (
   SELECT *
   FROM MEMBER_PRIVILEGE LEFT JOIN PRIVILEGE ON MEMBER_PRIVILEGE.pid = PRIVILEGE.id)
   WHERE MEMBER_PRIVILEGE.mid = MEMBER.id AND PRIVILEGE.id IS NULL
 )

SELECT *
FROM MEMBER
WHERE NOT EXISTS (
   SELECT *
   FROM MEMBER_PRIVILEGE LEFT JOIN PRIVILEGE ON MEMBER_PRIVILEGE.pid = PRIVILEGE.id)
   WHERE MEMBER_PRIVILEGE.mid = MEMBER.id AND PRIVILEGE.id IS NULL
 )

回复收藏 0 原文

流年里的时光 2024-12-18 21:19:10

试试这个::

Select name, (select count(id) from PRIVILIGE) as p_count
  From MEMBER m
  inner join MEMBER_PRIVILEGE mp on (m.id=mp.mid) 
  group by mp.mid having count(*) = p_count;

Try this ::

Select name, (select count(id) from PRIVILIGE) as p_count
  From MEMBER m
  inner join MEMBER_PRIVILEGE mp on (m.id=mp.mid) 
  group by mp.mid having count(*) = p_count;

回复收藏 0 原文

~没有更多了~