涉及“所有人"的SQL查询 [英] SQL queries involving ' for all'

问题描述

我无法获得有关如何为以下架构的A和B编写SQL查询的提示.

I could not get a hint on how to write SQL queries for A and B for the following schema.

Programme (Pid:int, Department:string...)
Employee (Eid:int, Department:string..)
Participation (Pid:int, Eid:int, ..)

A.所有员工参加的计划的名称

A. Names of programmes participated by all employees

B.参加其所有部门员工的姓名 程序.

B. Names of employees participating in all his department's programmes.

任何准则都会有所帮助.

Any guidelines would be helpful.

推荐答案

还没有尝试过，但这就是我的想法:

Haven't tried these, but this is what I would be thinking:

SELECT pg.Name 
FROM Participation AS p INNER JOIN Programme AS pg ON p.Pid = pg.Pid
GROUP BY p.Pid 
HAVING COUNT(*) = (SELECT COUNT(*) FROM Employeee)



SELECT e.Name 
FROM Participation AS p INNER JOIN Employee AS e ON p.Eid = e.Eid
                        INNER JOIN Programme AS pg ON pg.Pid = p.Pid
WHERE pg.Department = e.Department
GROUP BY p.Eid, e.Department, e.Name
HAVING COUNT(*) = (SELECT COUNT(*) 
                   FROM Programme AS pg2 
                   WHERE pg2.Department = e.Department)

这篇关于涉及“所有人"的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！