一个简单的连接是不够的，但另一个没有输出我想要的 [英] One simple join is not enough but another one doesn't output what i want

问题描述

我有一个表格，里面有一些存储在其中的应用程序的规则"

i have a table with some "rules" for na app stored in it

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)，这就是输出

and the other one is generated on the go with all the possible combinations from a defined set of numbers (e.g. this one i selected 2, 7, 16, 17, 18, 19), and this was the output

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 列对两个表进行内部连接.我进入了这个

And i want to inner join the two tables using only pi1, pi2, pi3 columns. i got into this

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,,

我做错了什么?

推荐答案

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

If you want to join these two tables using pi1,pi2,pi3 columns as keys then that's what you need to specify in your INNER JOIN ... ON statement as follows:

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;

这篇关于一个简单的连接是不够的，但另一个没有输出我想要的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！