至少一个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.我过去曾使用过它.一旦继续前进(您将其命名为Oracle，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屋！