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

本文介绍了SQL在表的两个副本之间使用WHERE子句依赖关系留下自连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下两句:

hello there bye!

在表sentence_words中表示为:

are represented in the table sentence_words by:

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

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

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:

WORD1 WORD2 there NULL

我试过了:

(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.

推荐答案

将 word_number + 1 要求移到 LEFT JOIN 中.

Move the word_number + 1 requirement into the 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

NECRO

虽然以上修复了 LEFT JOIN 的使用，但我建议根本不要使用连接...

Although the above fixes the use of LEFT JOIN, I would suggest not using joins at all...

SELECT sentence_id, MAX(CASE WHEN pos = 0 THEN word END) AS word1, MAX(CASE WHEN pos = 1 THEN word END) AS word2 FROM ( SELECT sentence_id, word_number - MY_START_NUMBER AS pos, word FROM sentence_words ) AS offset_sentence_words WHERE pos IN (0, 1) GROUP BY sentence_id