在同一张桌子上两次两次联接 [英] Double JOIN with the same table twice

问题描述

我在查询中有一个小问题，我将对同一张表两次进行两次JOIN.我有这2张桌子:

I have a little problem with a query where I would make a double JOIN with the same table twice. I have this 2 tables:

list_couples
|id |p_1      |p_2|
|1  |Topolino |Minnie|
|2  |Pippo    |Pluto|
|3  |Paperino |Paperone|

rank
|year |week |player   |points
|2015 |1    |Paperino |200
|2015 |1    |Pippo    |100
|2015 |1    |Paperone |50
|2015 |1    |Topolino |30
|2015 |1    |Minnie   |30
|2015 |2    |Paperino |300
|2015 |2    |Pippo    |150
|2015 |2    |Paperone |50
|2015 |2    |Pluto    |40

我会通过减少一年中一周内两个球员的总和来对列表list_couples中的对(p_1和p_2)进行排序.

I would order the couples (p_1 and p_2) in the table list_couples by decreasing sum points of two players for a week in a year.

当一对夫妇的玩家都在排行榜中时，我的查询有效，但是如果一个或两个都不在排行榜中，则不作为第1周的示例，在该周中，冥王星不在排行榜中1.

When players of couple are in the rank table both, my query works, but if one or both not are in rank table, it doesn't as example of week 1 where pluto isn't in the rank table at the week 1.

这是我的查询

SELECT i.*, r.*, r2.*,
             r.points + r2.points AS sum_points
        FROM list_couples AS l
        LEFT JOIN rank AS r  ON l.p_1=r.player
        LEFT JOIN rank AS r2 ON l.p_2=r2.player
        WHERE r.week='$week'  AND r.year='$year' AND
              r2.week='$week' AND r2.year='$year'
        ORDER BY sum_points DESC

使用此解决方案，我得到了这个结果 结果

With this solution I get this results result

|3 |Paperino |Paperone |2015 |1 |300 |2015 |1 |50 |350
|1 |Topolino |Minnie   |2015 |1 |30  |2015 |1 |30 |60

代替此

|3 |Paperino |Paperone |2015 |1 |300 |2015 |1 |50   |350
|2 |Pippo    |Pluto    |2015 |1 |100 |2015 |1 |NULL |100
|1 |Topolino |Minnie   |2015 |1 |30  |2015 |1 |30   |60

我该如何解决?

推荐答案

由于空记录，左侧联接表上的where子句限制消除了所需的结果...因此将限制移至联接本身，因此限制为在连接之前应用，从而保留空值记录.

The where clause limits on the left join tables are eliminating the desired results due to null records... So move the limits to the join itself so the limit is applied BEFORE the join, thus keeping the null value records.

SELECT i.*,r.*,r2.*, r.points+r2.points AS sum_points
FROM list_couples AS l
LEFT JOIN rank AS r ON l.p_1=r.player
  and r.week='$week' AND r.anno='$anno' 
LEFT JOIN rank AS r2 ON l.p_2=r2.player
  and  r2.week='$week' AND r2.anno='$year'
ORDER BY sum_points DESC

因为冥王星没有周/年(anno)，所以where子句消除了该记录.通过将限制移动到联接，将在联接发生之前应用筛选器，从而保持LEFT外部联接.

Because pluto has no week/year (anno) then the where clause is eliminating that record. by moving the limits to the join, the filter is applied before the join occurrs, thus keeping the LEFT outer join.

换一种说法，WHERE子句使左连接成为内部连接！

Put another way, the WHERE Clause is making the left join an inner one!

这篇关于在同一张桌子上两次两次联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！