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

I'm trying to write an efficient query like the following

我正在尝试编写如下的高效查询

"Get all rows that are deleted and that have no other row with the same value that are not-deleted"

“获取所有已删除的行，并且没有其他行具有相同的未删除的行”

Example.

例。

| 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
------------------------

The reason why the rows with foo aren't in the result set is because one of them has deleted = 0.

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

4 个解决方案

#1

We can achieve this in two ways .

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

Your Data looks like :

您的数据如下所示：

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)

Method 1 : Using Not Exists

方法1：使用不存在

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

Method 2 : Using Apply Operator

方法2：使用Apply Operator

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

#2

Check this query

检查此查询

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

Here's one article you might be interested in

这是您可能感兴趣的一篇文章

#3

In order to get the deleted values only not the others you could use subquery form where sub-query returns only the values which have been deleted with value of 1.

为了获取已删除的值而不是其他值，您可以使用子查询形式，其中子查询仅返回已删除的值为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
)

correlated sub-query might be useful

#4

If you like me like join clauses this is an option : )

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

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

#1