sql - 使用 SQL 从一组值中查找缺失值

Question

如何使用 SQL (Oracle DB) 从一组值中找到缺失值例如

SELECT NAME
FROM ACCOUNT
WHERE ACCOUNT.NAME IN ('FORD','HYUNDAI','TOYOTA','BMW'...)

(“IN”子句可能包含数百个值)如果 ACCOUNT 表中缺少“HYUNDAI”，我需要得到“HYUNDAI”的结果。

目前我使用上述查询的结果对原始值集进行Vlookup来查找缺失值，我想直接获取缺失值而不进行Vlookup。

谢谢基兰，

Answer 1

你把它弄反了。这样做:http://www.sqlfiddle.com/#!2/09239/3

SELECT Brand
FROM 
(
    -- Oracle can't make a row without a table, need to use DUAL dummy table
    select 'FORD' as Brand from dual union
    select 'HYUNDAI' from dual union
    select 'TOYOTA' fom dual union
    select 'BMW' from dual      
) x
where Brand not in (select BrandName from account)

示例帐户数据:

create table account(AccountId int, BrandName varchar(10));

insert into account(AccountId, BrandName) values
(1,'FORD'),
(2,'TOYOTA'),
(3,'BMW');

输出:

|   BRAND |
-----------
| HYUNDAI |

---

更好的是，将品牌具体化到表格中:

select *
from Brand
where BrandName not in (select BrandName from account)

输出:

| BRANDNAME |
-------------
|   HYUNDAI |

示例数据和实时测试:http://www.sqlfiddle.com/#!2/09239/1

CREATE TABLE Brand
    (`BrandName` varchar(7));

INSERT INTO Brand
    (`BrandName`)
VALUES
    ('FORD'),
    ('HYUNDAI'),
    ('TOYOTA'),
    ('BMW');


create table account(AccountId int, BrandName varchar(10));

insert into account(AccountId, BrandName) values
(1,'FORD'),
(2,'TOYOTA'),
(3,'BMW');