我该如何编写有效的查询来执行此操作

Question

我正在尝试编写一个有效的查询，如以下

"获取所有已删除且没有其他相同值的行的行"

示例。

| id | val   | deleted | 
========================
| 1  | "foo" |    1
------------------------
| 2  | "bar" |    1
------------------------
| 3  | "foo" |    0
------------------------
| 4  | "baz" |    1
------------------------
| 5  | "qux" |    0
------------------------
| 6  | "baz" |    1
------------------------

=========>

------------------------
| 2  | "bar" |    1
------------------------
| 4  | "baz" |    1
------------------------
| 6  | "baz" |    1
------------------------

不在结果集中的foo行的原因是因为其中一个具有deleted = 0。

Answer 1

检查此查询

select
    a.*
from
    myTable a
where
    not exists (
        select 1
        from mytable b
        where
            a.val = b.val
            and b.deleted = 0
    )

这是您可能对

Answer 2

我们可以通过两种方式实现这一目标。

您的数据看起来像：

SELECT * into #tab FROM(
VALUES(1,'foo',1),
(2,'bar',1),
(3,'foo',0),
(4,'baz',1),
(5,'qux',0),
(6,'baz',1)
) AS A (id,VAL,DELETED)

方法1：不存在

select * from #tab t
where DELETED=1 and 
not exists (select * from #tab t1 where t1.DELETED=0 and t1.VAL=t.val)

方法2：使用应用程序操作员

SELECT T.* FROM #tab T
OUTER APPLY (SELECT * FROM #tab T1 WHERE T.VAL=T1.VAL AND T1.DELETED=0) as a 
where T.DELETED=1 AND A.id IS NULL

Answer 3

以获取已删除的值，只有其他您可以使用subquery表单，其中sub-Query仅返回已删除的值1。

SELECT * FROM table 
WHERE val IN (
    SELECT val FROM table
    GROUP BY val
    HAVING COUNT(DISTINCT deleted) = 1 AND
           COUNT(DISTINCT case when deleted = 1 then deleted end) = 1
)

相关的子查询可能很有用

SELECT * FROM table t
    WHERE EXISTS (
        SELECT val FROM table
        WHERE val = t.val
        GROUP BY val
        HAVING COUNT(DISTINCT deleted) = 1 AND
               COUNT(DISTINCT case when deleted = 1 then deleted end) = 1
    )

Answer 4

如果您喜欢我喜欢加入条款，这是一个选项：(

declare @t table(id int, val varchar(50) ,deleted bit) 
insert into @t(id,val,deleted)
values 
(1,'foo',1)
,(2,'bar',1)
,(3,'foo',0)
,(4,'baz',1)
,(5,'qux',0)
,(6,'baz',1)
select *
from @t t1
left join(select distinct t2t1.val from @t t2t1 where deleted=0) t2 on t1.val=t2.val
where t2.val is null