sql - TSQL 在 IN 运算符中获取丢失的记录

Question

我在 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   |
+--------+-----------+

标签： sqlsql-servertsqlselectsql-in

Answer 1

使用派生表 usingVALUES用于所有有问题的城市，并使用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<>小提琴