sql-server – T-SQL“<> ANY(子查询)”

在Technet上它说

For example,the following query finds customers located in a territory not covered by any sales persons.

Use AdventureWorks2008R2;
GO
SELECT
    CustomerID
FROM
    Sales.Customer
WHERE
    TerritoryID <> ANY
    (
        SELECT
            TerritoryID
        FROM
            Sales.SalesPerson
    );

进一步

The results include all customers,except those whose sales territories are NULL,because every territory that is assigned to a customer is covered by a sales person. The inner query finds all the sales territories covered by sales persons,and then,for each territory,the outer query finds the customers who are not in one.

但该查询返回所有客户.
我将客户TerritoryID更新为没有sales.person的值,但该查询仍返回所有客户,而不是我期望的客户.

我错过了什么吗？
也许那篇关于technet的文章完全错了？
https://technet.microsoft.com/de-de/library/ms187074(v=sql.105).aspx(德国)

有一个客户的TerritoryID = 13

内部查询结果(SELECT TerritoryID FROM Sales.SalesPerson)：
4
2
4
3
6
五
1
4
6
1
1
6
9
1
8
10
7

在表中,Sales.Customer是CustomerID = 13的行,这是销售人员未涵盖的行.

解决方法

create table #t1
(
id int
)

insert into #t1
values(1),(2),(3)

如您所见,T1有三个值

现在让我们看看,如何工作

当’is Equal to’与any一起使用时,它就像IN一样

select * from #t1 where id=
any(select 0)--no result

何时使用>或<>,任何方法都可以获得大于最小值的所有值

select * from #t1 where id<>
any(select 1)--2,3

select * from #t1 where id<>
any(select 0)--1,2,3

如果子查询返回一个值,则外部查询将尝试获取大于内部查询的值

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!