mysql - 一对一加入MySQL

Question

我需要在一个公共(public)列上连接两个表，但我想在其他两列上保持一对一的关系。例如:

表_1

ID_C   ID_ROW_C   OPT
C      1          10   
C      2          10 

表_2

ID_F   ID_ROW_F   OPT
F      3          10   
F      4          10 

我的查询:

select *  
from table_1, table_2  
where table_1.OPT=table_2.OPT

结果

ID_C   ID_ROW_C   OPT   ID_F   ID_ROW_F  
C      1          10    F      3  
C      1          10    F      4  
C      2          10    F      3  
C      2          10    F      4  

期望的结果:

ID_C   ID_ROW_C   OPT   ID_F   ID_ROW_F
C      1          10    F      4  
C      2          10    F      3 

或

ID_C   ID_ROW_C   OPT   ID_F   ID_ROW_F
C      1          10    F      3  
C      2          10    F      4 

我该怎么办？

Answer 1

您需要做的是使用JOIN。

SELECT * FROM table_1
JOIN table_2
ON table_1.OPT = table_2.OPT

更多信息来自 MySQL 手册:https://dev.mysql.com/doc/refman/5.0/en/join.html

以及关于不同类型的 JOIN 的相关 Stack Overflow 讨论:What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?