PostgreSQL:左外部联接语法 [英] PostgreSQL: left outer join syntax

问题描述

我在CentOS 5.5上使用PostgreSQL 8.4.6，并有一个用户表:

I'm using PostgreSQL 8.4.6 with CentOS 5.5 and have a table of users:

# 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)

在这种情况下，我没有任何行，因为用户"DE2"本周尚未获得任何虚拟货币".

In this case I've got no rows, because the user 'DE2' hasn't earned any "virtual money" this week yet.

我想更改查询，使其始终返回现有用户的数据，如果他们本周未玩过游戏，则查询应返回0.

I'd like to change my query so that it always returns data for existing users and if they haven't played this week - then the query should return 0.

所以我想我需要一个左外连接，我正在尝试:

So I guess I need an outer left join and 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;

但是这些返回了很多具有不同用户的行，而不是只有id ='DE2'的行.

but these returns me a lot of rows with different users instead of just one with the id='DE2'.

我在做什么错了?

推荐答案

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;

这篇关于PostgreSQL:左外部联接语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！