gpt4 book ai didi

sql - 在 SQL Server 中选择文字作为表数据

转载 作者:行者123 更新时间:2023-12-02 07:32:56 26 4
gpt4 key购买 nike

考虑表 Address ,其中包含 Country、State 和其他数据字段。我想获取除 Country,State 组合为 (US, IL), (US,LA), (IND,DEL) 之外的所有记录

查询就像

Select * from Address a 
where not exists
(
select Country,State
(select 'US' as Country, 'IL' as State
union
select 'US' as Country, 'LA' as State
union
select 'IND' as Country, 'DEL' as State
) e
where e.Country != a.Country and e.State != a.state
)

如何轻松实现(用简单的子查询替换国家,联合的状态组合)?由于总数据不是很大,我现在最不关心性能。

我知道我可以创建表变量,使用 insert into 语法添加所有文字组合,并使用表变量表示不存在,但我觉得这对于小需求(不存在于 2 个变量)来说有点过分了。

最佳答案

看起来您的查询试图这样做:

select * 
from Address a
where not exists (
select *
from (
select 'US' as Country, 'IL' as State union all
select 'US' as Country, 'LA' as State union all
select 'IND' as Country, 'DEL' as State
) e
where e.Country = a.Country and
e.State = a.State
)

或者您无法使用派生表并仍然获得相同的结果
select *
from Address as a
where not (
a.Country = 'US' and a.State = 'IL' or
a.Country = 'US' and a.State = 'LA' or
a.Country = 'IND' and a.State = 'DEL'
)

关于sql - 在 SQL Server 中选择文字作为表数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19984439/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com