PostgreSQL：左外连接语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4579957/
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 syntax
提问by Alexander Farber
I'm using PostgreSQL 8.4.6 with CentOS 5.5 and have a table of users:
我在 CentOS 5.5 上使用 PostgreSQL 8.4.6 并有一个用户表：
# select * from pref_users where id='DE2';
id | first_name | last_name | female | avatar | city | lat | lng | login | last_ip | medals | logout
-----+------------+-----------+--------+------------------+---------------------+-----+-----+----------------------------+---------+--------+----------------------------
DE2 | Alex | | f | 2_1280837766.jpg | г. Бохум в Германии | | | 2011-01-02 19:26:37.790909 | | | 2011-01-02 19:29:30.197062
(1 row)
and another table listing their "virtual money" won in a game each week:
另一个表格列出了他们每周在一场游戏中赢得的“虚拟货币”：
# select * from pref_money where id='DE2';
id | money | yw
-----+-------+---------
DE2 | 66 | 2010-48
(1 row)
Then I'm trying to display both of these infos for a user, but I'm only interested in the user's money for the current week:
然后我试图为用户显示这两个信息，但我只对用户本周的钱感兴趣：
# select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login > u.logout as online
from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, 'YYYY-IW') and
u.id=m.id and
u.id='DE2'
order by m.money desc;
id | first_name | city | avatar | money | online
----+------------+------+--------+-------+--------
(0 rows)
In this case I've got no rows, because the user 'DE2' hasn't earned any "virtual money" this week yet.
在这种情况下，我没有任何行，因为用户“DE2”本周还没有赚取任何“虚拟货币”。
I'd like to change my query so that it always returns data for existing usersand if they haven't played this week - then the query should return 0.
我想更改我的查询，以便它始终返回现有用户的数据，如果他们本周没有玩过 - 那么查询应该返回 0。
So I guess I need an outer left joinand I'm trying:
所以我想我需要一个外部左连接，我正在尝试：
select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login > u.logout as online
from pref_users u left outer join pref_money m on (
m.yw=to_char(current_timestamp, 'YYYY-IW') and
u.id=m.id and
u.id='DE2')
order by m.money desc;
but these returns me a lot of rows with different users instead of just one with the id='DE2'.
但是这些返回给我很多不同用户的行，而不仅仅是带有 id='DE2' 的行。
What am I doing wrong please?
请问我做错了什么？
回答by Daniel
select u.id,
u.first_name,
u.city,
u.avatar,
coalesce(m.money,0),
u.login > u.logout as online
from pref_users u left outer join pref_money m on u.id=m.id
and m.yw=to_char(current_timestamp, 'YYYY-IW')
where u.id='DE2'
order by m.money desc;
回答by araqnid
You want to put the filter condition u.id='DE2'in the WHERE clause, leaving everything else in the ON clause. For every user where the ON doesn't match, it will still output the row with the data from uand nulls for m.
您想将过滤条件u.id='DE2'放在 WHERE 子句中，而将其他所有内容都放在 ON 子句中。对于 ON 不匹配的每个用户，它仍将输出包含来自u的数据和m 的空值的行。
回答by Ilya Kogan
Try moving the
尝试移动
u.id='DE2'
into a WHERE clause, like this:
变成一个 WHERE 子句，像这样：
select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login > u.logout as online
from pref_users u left outer join pref_money m on u.id=m.id
where m.yw=to_char(current_timestamp, 'YYYY-IW') and
u.id='DE2'
order by m.money desc;
