如何从两列中的一列中选择值,其中值包含在另一个表中?
来源表:
DataA | DataB |
---|---|
苹果 | 苹果 |
橙色 | Ora |
Pinea | 菠萝 |
计算机 | ABCD123 |
尝试:
select distinct DataC as result
from check_tbl
where DataC in (select distinct DataA
from source_tbl)
or DataC in ( select distinct DataB
from source_tbl)
数据示例:
create table source_tbl(
DataA varchar(30),
DataB varchar (30) );
insert into source_tbl values
('Apple','Apple'),
('Orange','Ora'),
('Pinea','Pineapple'),
('Computer','ABCD123');
create table check_tbl(
DataC varchar(30) );
insert into check_tbl values
('Apple'),
('Orange'),
('Pineapple');
结果:
result Apple Orange Pineapple
演示:https://dbfiddle.uk/?rdbms=sqlserver_2019&小提琴=8b0a13a07ae245b00616bb24b0a972b0
一个简单的UNION
就可以:
SELECT DataA
FROM Demo1 INNER JOIN DemoCheck ON Demo1.DataA = DemoCheck.DataC;
UNION
SELECT DataB
FROM Demo1 INNER JOIN DemoCheck ON Demo1.DataB = DemoCheck.DataC;
UNION
只检索一次值,即使它们存在于并集的两个部分中。