SQL在表的两个副本之间使用WHERE子句依赖关系留下自连接 [英] SQL left self-join with WHERE clause dependencies between the two copies of the table
问题描述
以下两句:
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
这篇关于SQL在表的两个副本之间使用WHERE子句依赖关系留下自连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!