时间戳

首页 > 解决方案 > where 语句取消了结果？

sql - where 语句取消了结果？

问题描述

我想UID从下面的示例中找到符合自相矛盾的 where 陈述但为真的数字。这个想法是为了证明如果“父”和“关闭”在数据中，则“打开”和“子”不存在。下面的示例应UID=789作为用例错误返回。

UID     Title   Business
123     Parent  Open
123     Child 1 Open
123     Child 2 Open
456     Parent  Closed
456     Child 1 Closed
456     Child 2 Closed
789     Parent  Closed
789     Child 1 Open
789     Child 2 Closed

我一无所有，

select UID from TABLE
where  
(TITLE = 'Parent' and Business = 'Closed') 
and
(TITLE like 'Child%' and Business = 'Open')

标签： sqloracle

解决方案

您可以聚合每个 UID 并用于HAVING检查两种条件的记录是否存在：

select uid
from TABLE
group by uid  
having count(case when title = 'Parent' and business = 'Closed' then 1 end) > 0
   and count(case when title like 'Child%' and business = 'Open' then 1 end) > 0;

推荐阅读