postgreSQL 语句选择具有特定值的列

Question

我需要一些帮助来做一个sql语句;我真的不知道如何应对这种情况。我有两个表，部门和员工 我想从中选择至少拥有一名员工且所有员工都来自巴塞罗那的部门的Dpt_num和Dpt_name

案例1

== Departments =======
| Dpt_num | Dpt_name |
|    1    |    A     |
|    2    |    B     |
== Employees ===================
| E_num | Dpt_num |  City      |
|  1    |   1     | Barcelona  |
|  2    |   1     | Barcelona  |

在这种情况下，结果应该是

Dpt_num Dpt_name
------------------
1       A

案例2

== Departments =======
| Dpt_num | Dpt_name |
|    1    |    A     |
|    2    |    B     |
== Employees ==================
| E_num | Dpt_num | City      |
|   1   |    1    | Barcelona |
|   2   |    1    | Madrid    |

在这种情况下，结果应为空。

例如，我尝试过这个，但它似乎效率很低，而且并非在所有情况下都有效

select
num_dpt, nom_dpt
from
departements
where
1 = (select count(distinct e.ciutat_empl)
from empleats e
where e.num_dpt = num_dpt)
and not exists (select * from empleats e
where e.ciutat_empl != 'BARCELONA' and e.num_dpt = num_dpt);

我真的很感激任何帮助。谢谢！

Answer 1

您想沿着在where子句中进行过滤的道路前进。 然后，使用exists和not exists：

select d.num_dpt, d.nom_dpt 
from departaments d
where exists (select 1
from empleats e
where e.num_dpt = d.num_dpt and e.ciutat_empl = 'BARCELONA' 
) and
not exists (select 1
from empleats e
where e.num_dpt = d.num_dpt and e.ciutat_empl <> 'BARCELONA' 
);

第一个条件检查至少有一名员工来自巴塞罗那。 第二个检查没有员工来自任何其他城市。

您的版本中的一个主要问题是您的关联子句：

e.num_dpt = num_dpt

你认为这是在做：

e.num_dpt = departaments.num_dpt

但它确实在做：

e.num_dpt = e.num_dpt

始终限定列名称。 当查询中有多个表引用时，这一点尤其重要。

Answer 2

连接表格，按部门分组，并检查巴塞罗那的员工人数是否等于该部门所有雇员的计数。

SELECT d.dpt_num,
d.dpt_name
FROM departments d
INNER JOIN employees e
ON e.dpt_num = d.dpt_num
GROUP BY d.dpt_num,
d.dpt_name
HAVING count(CASE
WHEN e.city = 'Barcelona' THEN
1
END) = count(*);

Answer 3

我相信这应该有效：

select d.dpt_num, d.dpt_name
from departments d
inner join employees e on
d.dpt_num = e.dpt_num
group by d.dpt_num, d.dpt_name
having count(*) = sum(case when e.city = 'Barcelona' then 1 else 0 end)

• INNER JOIN确保至少有 1 名员工
• HAVING count(*) = sum(case when e.city = 'Barcelona' then 1 else 0 end)确保所有员工都来自巴塞罗那

Answer 4

demo： db<>fiddle

SELECT dpt_num, dpt_name
FROM (
SELECT d.dpt_num, d.dpt_name, array_agg(city) as cities
FROM dept d
JOIN empl e
ON d.dpt_num = e.dpt_num
GROUP BY d.dpt_num, d.dpt_name
) s
WHERE 'Barcelona' = ALL(cities)

聚合城市，然后您可以使用ALL运算符进行过滤，该运算符检查所有数组元素是否都符合条件。

Answer 5

一般来说，您将COUNT(*)与此类问题的COUNT(some condition)进行比较：

SELECT *
FROM Departments
WHERE EXISTS (
SELECT 1
FROM   Employees
WHERE  Employees.Dpt_num = Departments.Dpt_num
HAVING COUNT(*) > 0 -- it is possible to get a 0 if where did not match
AND    COUNT(*) = COUNT(CASE WHEN Employees.City = 'Barcelona' THEN 1 END)
)

德铁小提琴

Answer 6

请尝试下面的查询

select a.dpt_number,a.dpt_name from yy_department a 
where exists (select 'x' from yy_employees y where y.dpt_number = a.dpt_number and y.city = 'Barcelona') 
and not exists (select 'x' from yy_employees y where y.dpt_number = a.dpt_number and nvl(y.city,'x') <> nvl('Barcelona','y'))