SQL在表的两个副本之间留下了自我连接与WHERE子句的依赖关系

The following two sentences:

以下两句话:

hello there
bye!

are represented in the table sentence_words by:

在表sentence_words中表示:

WORD_ID  SENTENCE_ID    WORD    WORD_NUMBER
10       1              hello   1
11       1              there   2
12       2              bye!    1

I want to do an outer join query that gives me the results:

我想做一个外连接查询,给我结果:

WORD1      WORD2
hello      there
bye!       NULL

Note that I may want to start in the middle of the sentence so I cannot assume that word2 has word_number = 2. If I choose my_start_number = 2 then the query should give me:

请注意,我可能想在句子的中间开始,所以我不能假设word2有word_number = 2.如果我选择my_start_number = 2那么查询应该给我:

WORD1   WORD2
there   NULL

I tried:

(my_start_number = 1)

select  s1.word word1, s2.word word2
from sentence_words s1
left join sentence_words s2
on s1.sentence_id = s2.sentence_id
where s1.word_number = my_start_number
 and (s2.word_number = s1.word_number +1 or s2.word_number is null);

That only gives me a result if there are two words in the sentence. I'm not sure what to do that isn't way complicated.

如果句子中有两个单词,那只会给我一个结果。我不知道该怎么做并不复杂。

2 个解决方案

#1

Move the word_number + 1 requirement into the LEFT JOIN.

将word_number + 1要求移动到LEFT JOIN中。

SELECT
  s1.word word1, s2.word word2
FROM
  sentence_words s1
LEFT JOIN
  sentence_words s2
    ON  s2.sentence_id = s1.sentence_id
    AND s2.word_number = s1.word_number + 1
WHERE
  s1.word_number = my_start_number

#2

Dems answer is absolutely the correct one. I decided to write this answer to explain the reason that your original solution doesn't work. This is because you are trying to filter the following result set of the left outter join (showing all columns, with some names abbreviated to fit):

Dems的回答绝对正确。我决定写这个答案来解释原始解决方案不起作用的原因。这是因为您尝试过滤左侧扩展连接的以下结果集(显示所有列,其中一些名称缩写为适合):

s1.WORD_ID s1.SENT_ID s1.WORD  s1.WORD_NUM s2.WORD_ID s2.SENT_ID s2.WORD  s2.WORD_NUM
10         1          hello    1           10         1          hello    1
10         1          hello    1           11         1          there    2
11         1          there    2           10         1          hello    1
11         1          there    2           11         1          there    2
12         2          bye!     1           12         2          bye!     1

Now, take a look at your where clause:

现在,看看你的where子句:

where s1.word_number = my_start_number  
 and (s2.word_number = s1.word_number +1 or s2.word_number is null);

... and it should be relatively easy to see why it doesn't work. For example, s2.word_number is never NULL.

...而且应该相对容易理解为什么它不起作用。例如,s2.word_number永远不会为NULL。

#1