tsql获取in操作符中丢失的记录

um6iljoc 于 2021-08-13 发布在 Java

关注(0)|答案(4)|浏览(307)

我在sql server中有一个如下表：

create table address (id int, city varchar(10));

insert into address values (1, 'Rome');
insert into address values (2, 'Dallas');
insert into address values (3, 'Cracow');
insert into address values (4, 'Moscow');
insert into address values (5, 'Liverpool');
insert into address values (6, 'Cracow');
insert into address values (7, 'Seoul');

我正在写一个关于 IN 操作员组件

SELECT City 
FROM address 
WHERE city IN ('Rome', 'Mumbai', 'Dallas', 'Delhi', 'Moscow')

我可以得到结果，但我想得到表中丢失或不可用记录的列表，如

|  City  |  Status   |
+--------+-----------+
| Rome   | Available |
| Dallas | Available |
| Moscow | Available |
| Mumbai | Missing   |
| Delhi  | Missing   |
+--------+-----------+

sql sql-server select tsql sql-in

来源：https://stackoverflow.com/questions/62075765/tsql-to-get-missing-records-inside-in-operator

4条答案

按热度按时间

kmpatx3s1#

为此，我建议使用行构造函数 values() 而不是 in :

select c.city, case when a.city is null then 'Missing' else 'Available' end status
from values(('Rome'), ('Mumbai'), ('Dallas'), ('Delhi'), ('Moscow')) c(city)
left join address a on a.city = c.city

如果有重复的 city 在 address 那就坐table吧 exists 是更好的选择：

select 
    c.city, 
    case when exists(select 1 from address a where a.city = c.city)
        then 'Available'
        else 'Missing' 
end status
from values(('Rome'), ('Mumbai'), ('Dallas'), ('Delhi'), ('Moscow')) c(city)

赞(0）回复(0）举报 2021-08-13

h43kikqp2#

使用派生表 VALUES 所有的城市和 CASE 带的表达式 EXISTS 检查是否存在城市地址。

SELECT city.name city,
       CASE
         WHEN EXISTS (SELECT *
                             FROM address
                             WHERE address.city = city.name) THEN
           'Available'
         ELSE
           'Missing'
       END status
       FROM (VALUES ('Rome'),
                    ('Mumbai'),
                    ('Dallas'),
                    ('Delhi'),
                    ('Moscow')) city (name);

db<>小提琴

赞(0）回复(0）举报 2021-08-13

pod7payv3#

你可以用iif

SELECT [City], 
   IIF([City] IN('Rome', 'Mumbai', 'Dallas', 'Delhi', 'Moscow'), 'Available', 'Missing') STATUS
FROM [Address];

结果

赞(0）回复(0）举报 2021-08-13

disho6za4#

使用cte：

with CitySearch (city) as (
select 'Rome' union all
select 'Mumbai'  union all
select 'Dallas'  union all
select 'Delhi'  union all
select 'Moscow' 
)
select t1.city,
case when t2.city is null then 'Missing' else 'Available' end Status
from CitySearch t1
left outer join address t2 on t1.city=t2.city
order by 2

赞(0）回复(0）举报 2021-08-13

我来回答

tsql获取in操作符中丢失的记录

4条答案

相关问题

热门标签

最新问答