PostgreSQL LEFT OUTER JOIN查询语法
发布时间:2020-12-13 16:02:17 所属栏目:百科 来源:网络整理
导读:可以说我有一个table1: id name------------- 1 "one" 2 "two" 3 "three" 和一个带有第一个外键的table2: id tbl1_fk option value------------------------------- 1 1 1 1 2 2 1 1 3 1 2 1 4 3 2 1 现在我想作为查询结果: table1.id | table1.name | op
可以说我有一个table1:
id name ------------- 1 "one" 2 "two" 3 "three" 和一个带有第一个外键的table2: id tbl1_fk option value ------------------------------- 1 1 1 1 2 2 1 1 3 1 2 1 4 3 2 1 现在我想作为查询结果: table1.id | table1.name | option | value ------------------------------------- 1 "one" 1 1 2 "two" 1 1 3 "three" 1 "one" 2 1 2 "two" 3 "three" 2 1 我如何实现这一目标? 我已经尝试过: SELECT table1.id,table1.name,table2.option,table2.value FROM table1 AS table1 LEFT outer JOIN table2 AS table2 ON table1.id = table2.tbl1fk 但结果似乎省略了空值: 1 "one" 1 1 2 "two" 1 1 1 "one" 2 1 3 "three" 2 1 解决:感谢Mahmoud Gamal :(加上GROUP BY) SELECT t1.id,t1.name,t2.option,t2.value FROM ( SELECT t1.id,t2.option FROM table1 AS t1 CROSS JOIN table2 AS t2 ) AS t1 LEFT JOIN table2 AS t2 ON t1.id = t2.tbl1fk AND t1.option = t2.option group by t1.id,t2.value ORDER BY t1.id,t1.name 解决方法
您必须使用CROSS JOIN从第一个表中获取每个可能的名称组合以及第二个表中的选项.然后LEFT JOIN这些组合与第二个表.就像是:
SELECT t1.id,t2.option FROM table1 AS t1 CROSS JOIN table2 AS t2 ) AS t1 LEFT JOIN table2 AS t2 ON t1.id = t2.tbl1_fk AND t1.option = t2.option SQL Fiddle Demo (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |