至少有一个 X 但没有 Ys 查询 [英] At least one X but no Ys Query

问题描述

我偶尔会遇到这种模式，但我还没有找到非常令人满意的解决方法.

I come across this pattern occasionally and I haven't found a terribly satisfactory way to solve it.

假设我有一个 employee 表和一个 review 表.每个员工可以有多个评论.我想找到所有至少有一个好"评论但没有坏"评论的 employee.

Say I have a employee table and an review table. Each employee can have more than one review. I want to find all the employees who have at least one "good" review but no "bad" reviews.

在事先不知道员工 ID 的情况下，我还没有弄清楚如何使子选择工作，而且我还没有想出正确的联接组合来实现这一点.

I haven't figured out how to make subselects work without knowing the employee ID before hand and I haven't figured out the right combination of joins to make this happen.

有没有办法做到这一点没有存储过程、函数或带来数据服务器端?我已经让它与那些一起工作，但我确信还有另一种方法.

Is there a way to do this WITHOUT stored procedures, functions or bringing the data server side? I've gotten it to work with those but I'm sure there's another way.

推荐答案

由于您还没有发布您的数据库结构，我做了一些假设和简化(关于 rating 列，可能是数字而不是字符字段).相应调整.

Since you haven't posted your DB Structure, I made some assumptions and simplifications (regarding the rating column, which probably is number and not a character field). Adjust accordingly.

select distinct e.EmployeeId, e.Name
from employee e
left join reviews r1 on e.EmployeeId = r1.EmployeeId and r1.rating = 'good'
left join reviews r2 on e.EmployeeId = r2.EmployeeId and r1.rating = 'bad'
where r1.ReviewId is not null --meaning there's at least one
and r2.ReviewId is null --meaning there's no bad review

解决方案 2:分组依据和条件计数过滤

select e.EmployeeId, max(e.Name) Name
from employee e
left join reviews r on e.EmployeeId = r.EmployeeId
group by e.EmployeeId
having count(case r.rating when 'good' then 1 else null end) > 0
and  count(case r.rating when 'bad' then 1 else null end) = 0

两种解决方案都与 SQL ANSI 兼容，这意味着它们都可以与任何完全支持 SQL ANSI 标准的 RDBMS 风格一起使用(大多数 RDBMS 都是如此).

Both solutions are SQL ANSI compatible, which means both work with any RDBMS flavor that fully support SQL ANSI standards (which is true for most RDBMS).

正如@onedaywhen 所指出的，该代码在 MS Access 中不起作用(尚未测试，我相信他在该主题上的专业知识).

As pointed out by @onedaywhen, the code will not work in MS Access (have not tested, I'm trusting in his expertise on the subject).

但我对此有一个说法(这可能会让一些人感到不安):我几乎不认为 MS Access 是一个 RDBMS.我过去曾使用过它.一旦你继续前进(甲骨文、SQL Server、Firebird、PostGreSQL、MySQL 等等)，你就再也不想回来了.认真的.

But I have one saying on this (which might make some people upset): I hardly consider MS Access a RDBMS. I have worked with it in the past. Once you move on (Oracle, SQL Server, Firebird, PostGreSQL, MySQL, you name it), you do not ever want to come back. Seriously.

这篇关于至少有一个 X 但没有 Ys 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！