mysql - 将一个表的项目搜索到另一个表中保存结果

Question

我有两个表:

Table 1

+--------+-----+-----+  
| name   | id1 | id2 |   
+--------+-----+-----+    
| luis   | 1   | 1.2 |  
+--------+-----+-----+   
| carlos | 2   | 1.3 |  
+--------+-----+-----+  
| george | 3   | 1.5 |  
+--------+-----+-----+   

Table 2

+--------+-----+-----+  
| points | id1 | id2 |  
+--------+-----+-----+   
| 100    | 1   | 1.2 |  
+--------+-----+-----+   
| 50     | 6   | 2   |   
+--------+-----+-----+   
| 48     | 3   | 1.5 |   
+--------+-----+-----+   

我想在两个表中搜索所有id1和id2相等的情况，并将第二个表的记录保存在一个新表中。

对于这种情况，它将是:

Table 3

+--------+-----+-----+   
| points | id1 | id2 |   
+--------+-----+-----+   
| 100    | 1   | 1.2 |        
+--------+-----+-----+   
| 48     | 3   | 1.5 |       
+--------+-----+-----+   

Answer 1

您可以使用 exists 或 inner join 来执行此操作。

insert into table3(points,id1,id2)
select points,id1,id2
from table2 t2
where exists (select 1 from table1 t1 where t1.id1=t2.id1 and t1.id2=t2.id2)