SQL PostgreSQL LEFT OUTER JOIN 查询语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15615419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL LEFT OUTER JOIN query syntax
提问by kosta
Lets say I have a table1:
假设我有一个table1：
id name
-------------
1 "one"
2 "two"
3 "three"
And a table2with a foreign key to the first:
还有一个table2带有外键的第一个：
id tbl1_fk option value
-------------------------------
1 1 1 1
2 2 1 1
3 1 2 1
4 3 2 1
Now I want to have as a query result:
现在我想作为查询结果：
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
How do I achieve that?
我如何做到这一点？
I already tried:
我已经尝试过：
SELECT
table1.id,
table1.name,
table2.option,
table2.value
FROM table1 AS table1
LEFT outer JOIN table2 AS table2 ON table1.id = table2.tbl1fk
but the result seems to omit the null vales:
但结果似乎省略了空值：
1 "one" 1 1
2 "two" 1 1
1 "one" 2 1
3 "three" 2 1
SOLVED: thanks to Mahmoud Gamal: (plus the GROUP BY) Solved with this query
已解决：感谢 Mahmoud Gamal：（加上 GROUP BY）已通过此查询解决
SELECT
t1.id,
t1.name,
t2.option,
t2.value
FROM
(
SELECT t1.id, t1.name, 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, t1.name, t2.option, t2.value
ORDER BY t1.id, t1.name
回答by Mahmoud Gamal
You have to use CROSS JOINto get every possible combination of namefrom the first table with the optionfrom the second table. Then LEFT JOINthese combination with the second table. Something like:
您必须使用从第一个表和第二个表中CROSS JOIN获取所有可能的组合。然后将这些与第二个表结合起来。就像是：nameoptionLEFT JOIN
SELECT
t1.id,
t1.name,
t2.option,
t2.value
FROM
(
SELECT t1.id, t1.name, 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
SQL 小提琴演示
回答by Erwin Brandstetter
Simple version: option = group
简单版本：选项 = 组
It's not specified in the Q, but it seems like option is supposed to define a group somehow. In this case, the query can simply be:
它没有在 Q 中指定，但似乎 option 应该以某种方式定义一个组。在这种情况下，查询可以简单地为：
SELECT t1.id, t1.name, t2.option, t2.value
FROM (SELECT generate_series(1, max(option)) AS option FROM table2) o
CROSS JOIN table1 t1
LEFT JOIN table2 t2 ON t2.option = o.option AND t2.tbl1_fk = t1.id
ORDER BY o.option, t1.id;
Or, if options are not numbered in sequence, starting with 1:
或者，如果选项没有按顺序编号，则以1：
...
FROM (SELECT DISTINCT option FROM table2) o
...
Returns:
返回：
id | name | option | value
----+-------+--------+-------
1 | one | 1 | 1
2 | two | 1 | 1
3 | three | |
1 | one | 2 | 1
2 | two | |
3 | three | 2 | 1
- Fasterand cleaner, avoiding the big
CROSS JOINand the bigGROUP BY. - You get distinct rowswith a group number (
grp) per set. - Requires Postgres 8.4+.
- 更快和更清洁，避免了大
CROSS JOIN和大GROUP BY。 - 您会得到不同的行，
grp每个行带有一个组号 ( )。 - 需要 Postgres 8.4+。
More complex: group indicated by sequence of rows
更复杂：由行序列指示的组
WITH t2 AS (
SELECT *, count(step OR NULL) OVER (ORDER BY id) AS grp
FROM (
SELECT *, lag(tbl1_fk, 1, 2147483647) OVER (ORDER BY id) >= tbl1_fk AS step
FROM table2
) x
)
SELECT g.grp, t1.id, t1.name, t2.option, t2.value
FROM (SELECT generate_series(1, max(grp)) AS grp FROM t2) g
CROSS JOIN table1 t1
LEFT JOIN t2 ON t2.grp = g.grp AND t2.tbl1_fk = t1.id
ORDER BY g.grp, t1.id;
Result:
结果：
grp | id | name | option | value
-----+----+-------+--------+-------
1 | 1 | one | 1 | 1
1 | 2 | two | 1 | 1
1 | 3 | three | |
2 | 1 | one | 2 | 1
2 | 2 | two | |
2 | 3 | three | 2 | 1
How?
如何？
Explaining the complex version ...
解释复杂的版本......
Every set is started with a
tbl1_fk<= the last one. I check for this with the window functionlag(). To cover the corner case of the first row (no preceding row) I provide the biggest possible integer2147483647the default forlag().With
count()as aggregate window function I add the running count to each row, effectively forming the group numbergrp.I could get a single instance for every group with:
(SELECT DISTINCT grp FROM t2) gBut it's faster to just get the maximum and employ the nifty
generate_series()for the reducedCROSS JOIN.This
CROSS JOINproduces exactly the rows we need without any surplus. Avoids the need for a laterGROUP BY.LEFT JOIN t2to that, usinggrpin addition totbl1_fkto make it distinct.Sort any way you like - which is possible now with a group number.
每个集合都以
tbl1_fk<= 最后一个开始。我用窗口函数lag()检查了这一点。为了覆盖第一行（没有前一行）的极端情况，我提供了最大可能的整数2147483647作为lag().随着
count()作为骨料窗函数我加上运行中计每一行，有效地形成了组号grp。我可以为每个组获得一个实例：
(SELECT DISTINCT grp FROM t2) g但是获得最大值并
generate_series()为减少的CROSS JOIN.这
CROSS JOIN正好产生了我们需要的行，没有任何剩余。避免了以后的需要GROUP BY。LEFT JOIN t2对此，使用grp除了tbl1_fk使其与众不同。以您喜欢的任何方式排序 - 现在可以使用组号进行排序。
回答by PSR
try this
尝试这个
SELECT
table1.id, table1.name, table2.option, table2.value FROM table1 AS table11
JOIN table2 AS table2 ON table1.id = table2.tbl1_fk
回答by user1389698
This is enough:
这就够了：
select * from table1 left join table2 on table1.id=table2.tbl1_fk ;
select * from table1 left join table2 on table1.id=table2.tbl1_fk ;
