如何从表1中获取数据，基于忽略表2中存在的两列组合的数据

本文介绍了如何从表1中获取数据，基于忽略表2中存在的两列组合的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两张桌子 TABLE1有两列 Col1 Col2 A 1 A 2 A 3 B 1 B 2 B 3 C 1 C 2 C 3 表2有两列 Col1 Col2 A 1 B 2 B 3 我希望输出作为第一个表的数据而忽略第二个表中的数据。 TABLE1中的数据是两列Col1和Col2的组合 输出 Col1 Col2 A 2 A 3 B 1 C 1 C 2 C 3 我的尝试： 我尝试了一些解决方案 SELECT Col1，Col2 FROM Table1 t1 内部联接表2 t2 t1.Col1<> ; （t2.Col1） 和 t1.Col2<> （t2.Col2） 另外，我尝试了左连接和提取但没有得到正确的解决方案。 有人请提供上述查询的任何解决方案。在此先感谢。

I have two tables TABLE1 with two columns Col1 Col2 A 1 A 2 A 3 B 1 B 2 B 3 C 1 C 2 C 3 TABLE 2 with two columns Col1 Col2 A 1 B 2 B 3 I want output as data of the first table with ignoring data from the second table. The data in TABLE1 is in the combination of two columns Col1 and Col2 Output Col1 Col2 A 2 A 3 B 1 C 1 C 2 C 3 What I have tried: I have tried some solutions SELECT Col1, Col2 FROM Table1 t1 inner join Table2 t2 on t1.Col1 <> (t2.Col1) and t1.Col2 <> (t2.Col2) Also, I tried left join and extract but not got the correct solution for it. Could someone please provide any solution for the above query. Thanks in advance.

推荐答案

嗯，还有 EXCEPT和INTERSECT运算符 [ ^ ]： Um, there are the EXCEPT and INTERSECT operators[^]: SELECT * FROM Table1 EXCEPT SELECT * FROM Table2

试试这个 Try this DECLARE @TABLE1 TABLE ( Col1 VARCHAR(50), Col2 int ) DECLARE @TABLE2 TABLE ( Col1 VARCHAR(50), Col2 int ) INSERT INTO @TABLE1 SELECT 'A' AS Col1, 1 AS Col2 UNION ALL SELECT 'A' AS Col1, 2 AS Col2 UNION ALL SELECT 'A' AS Col1, 3 AS Col2 UNION ALL SELECT 'B' AS Col1, 1 AS Col2 UNION ALL SELECT 'B' AS Col1, 2 AS Col2 UNION ALL SELECT 'B' AS Col1, 3 AS Col2 UNION ALL SELECT 'C' AS Col1, 1 AS Col2 UNION ALL SELECT 'C' AS Col1, 2 AS Col2 UNION ALL SELECT 'C' AS Col1, 3 AS Col2 INSERT INTO @TABLE2 SELECT 'A' AS Col1, 1 AS Col2 UNION ALL SELECT 'B' AS Col1, 2 AS Col2 UNION ALL SELECT 'B' AS Col1, 3 AS Col2 SELECT TABLE1.Col1,TABLE1.Col2 FROM ( SELECT *, (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) AS MultiCol FROM @TABLE1 ) AS TABLE1 WHERE TABLE1.MultiCol NOT IN ( SELECT (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) AS MultiCol FROM @TABLE2 ) -- OR SELECT * FROM @TABLE1 WHERE (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) NOT IN ( SELECT (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) AS MultiCol FROM @TABLE2 )