mysql - 一个简单的连接是不够的，但另一个连接不能输出我想要的内容

Question

我有一个表，其中存储了某个应用程序的一些“规则”

id,pi1,pi2,pi3
"1","1",,
"2","2",,
"3","3",,
"4","4",,
"5","5",,
"6","6",,
"7","7",,
"8","8",,
"9","9",,
"10","10",,
"11","11",,
"12","12",,
"13","13",,
"14","14",,
"15","15",,
"16","16",,
"17","17",,
"18","18",,
"19","19",,
"20","1","13",
"21","1","18",
"22","2","3",
"23","2","4",
"24","2","6",
"25","2","7",
"26","2","7","16"
"27","2","7","19"
"28","2","16",
"29","2","17",
"30","2","18",
"31","2","19",
"32","3","10",
"33","3","12",
"34","3","16",
"35","4","16",
"36","4","19",
"37","5","18",
"38","6","11",
"39","6","13",
"40","6","18",
"41","6","19",
"42","7","16",
"43","7","18",
"44","7","19",
"45","8","13",
"46","8","18",
"47","9","11",
"48","9","16",
"49","9","18",
"50","9","19",
"51","10","12",
"52","10","16",
"53","11","13",
"54","11","18",
"55","12","18",
"56","13","16",
"57","13","18",
"58","16","18",
"59","17","18",
"60","18","19",
"61","4","18",

另一个是根据一组定义的数字的所有可能组合动态生成的(例如，我选择了 2, 7, 16, 17, 18, 19)，这就是输出

idPI,pi1,pi2,pi3
"1","2",,
"2","7",,
"3","16",,
"4","17",,
"5","18",,
"6","19",,
"7","2","7",
"8","2","16",
"9","2","17",
"10","2","18",
"11","2","19",
"12","7","16",
"13","7","17",
"14","7","18",
"15","7","19",
"16","16","17",
"17","16","18",
"18","16","19",
"19","17","18",
"20","17","19",
"21","18","19",
"22","2","7","16"
"23","2","7","17"
"24","2","7","18"
"25","2","7","19"
"26","2","16","17"
"27","2","16","18"
"28","2","16","19"
"29","2","17","18"
"30","2","17","19"
"31","2","18","19"
"32","7","16","17"
"33","7","16","18"
"34","7","16","19"
"35","7","17","18"
"36","7","17","19"
"37","7","18","19"
"38","16","17","18"
"39","16","17","19"
"40","16","18","19"
"41","17","18","19"

我想仅使用 pi1、pi2、pi3 列来内部联接两个表。我进入了这个

SELECT DISTINCT table1.pi1, table1.pi2, table1.pi3, 
FROM db.table1
inner JOIN db.table2 c1
ON db.table1.pi1 = c1.pi1

结果如下

pi1,pi2,pi3,
2,,,
7,,,
16,,,
17,,,
18,,,
19,,,
2,3,, //this wrong shouldn't be here
2,4,, //this wrong shouldn't be here
2,6,, //this wrong shouldn't be here
2,7,,
2,7,16,
2,7,19,
2,16,,
2,17,,
2,18,,
2,19,,
7,16,,
7,18,,
7,19,,
16,18,,
17,18,,
18,19,,

我做错了什么？

Answer 1

如果您想使用 pi1、pi2、pi3 列作为键来连接这两个表，那么您需要在 INNER JOIN ... ON 语句中指定，如下所示:

SELECT t1.pi1, t1.pi2, t1.pi3
FROM table1 AS t1 
INNER JOIN table2 AS t2
ON t1.pi1 = t2.pi AND t1.pi2 = t2.pi2 AND t1.pi3 = t2.pi3;