匹配两个数据集中列的排列

I have two sets of data (csv files) which contain occurences of key database columns and the number of times they appear. (original database tables are no longer available)

我有两组数据(csv文件)，它们包含关键数据库列的出现次数和出现次数。(原始数据库表不再可用)

For example:

例如:

set 1

组1

column 1  column 2  column 3  count
1         2         3         100
1         2         2         200
3         1         1         700

set 2

组2

column 1  column 2  column 3  count
1         2         3         500
1         2         2         133
1         1         1         100

I need some way to compare the two tables to tell me if there is a combination in set 1 that is not present in set 2.

我需要某种方法来比较这两个表，以便告诉我集合1中是否存在集合2中不存在的组合。

I'm thinking of writing a script which will loop through set one with a nested loop for set 2 but I was wondering if there was a better way to do this.

我正在考虑编写一个脚本，它将使用set 2的嵌套循环遍历set one，但我想知道是否有更好的方法来实现这一点。

Open to suggestions?

开放的建议吗?

Thanks!

谢谢!

2 个解决方案

#1

You didn't specify your DBMS so this is ANSI SQL:

你没有指定你的DBMS这是ANSI SQL:

To get the combinations in table2 that are not in table1:

为了得到表2中没有的组合表1:

select col1, col2, col3
from table_1
except
select col1, col2, col3
from table_2;

To get the combinations in table1 that are not in table2:

得到表1中没有表2的组合:

select col1, col2, col3
from table_2
except 
select col1, col2, col3
from table_1;

To get both in one statement:

把两者都写在一个语句中:

(
  select col1, col2, col3
  from table_1
  except
  select col1, col2, col3
  from table_2
)
union all
(
  select col1, col2, col3
  from table_2
  except
  select col1, col2, col3
  from table_1
);

SQLFiddle example: http://sqlfiddle.com/#!15/71566/1

SQLFiddle示例:http://sqlfiddle.com/ ! 15/71566/1

#2

No loops please :)

不循环请:)

select t1.* 
from t1 left join t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3
where t2.col1 is null

Left joins create nulls where no record is found. Left join t1 to t2 and all times that t2 is null means a record in t1 has no match in t2.

左连接创建无记录的null。左连接t1到t2所有时间t2为空意味着t1中的记录在t2中不匹配。

Change this to right join and where t1.col1 is null and you'll see all of t2 not in t1 as well.

把它改成右连接和t1。col1是空的，你会看到所有的t2在t1中也不是。

#1