我认为这里的困惑是您假设的值为null
text_id=3,但实际上 没有匹配的行 。考虑以下简化版本:
select *from text_trigram where text_id in (3)
如果没有带有的条目,则不会返回任何行
text_id=3。它不会伪造一行带有一串空值的行。
为了即使没有匹配的数据也强制存在一行,您可以创建一个包含这些ID的表表达式,例如
select * from( values (1), (2), (3)) as required_ids ( text_id );
然后是
LEFT JOIN您的数据,因此您可以
NULL找到没有匹配数据的地方:
select *from ( values (1), (2), (3) ) as required_ids ( text_id )left join text_trigram on text_trigram.text_id = required_ids.text_id;
要进行第一个查询,请注意两件事:
count
忽略空值,因此count(text_trigram.text_id)
将仅对在text_trigram
表中找到匹配项的行进行计数任何额外条件都需要放在的
on
子句中left join
,以便它们从中消除行text_trigram
,而不是整个查询中的行select count(text_trigram.text_id), required_ids.text_id
from
(
values (1), (2), (3)
) as required_ids ( text_id )
left join text_trigram
on text_trigram.text_id = required_ids.text_id
and text_trigram.trigram_id = 1
group by text_id
order by text_id;
此更改为每个排列
text_id并
trigram_id只想涉及额外的表表达式和
CROSS JOIN:
select required_text_ids.text_id, required_trigram_ids.trigram_id, count(text_trigram.text_id)from ( values (1), (2), (3) ) as required_text_ids( text_id )cross join ( values (1), (2), (3) ) as required_trigram_ids( trigram_id )left join text_trigram on text_trigram.text_id = required_text_ids.text_id and text_trigram.trigram_id = required_trigram_ids.trigram_idgroup by required_text_ids.text_id, required_trigram_ids.trigram_idorder by required_text_ids.text_id, required_trigram_ids.trigram_id;
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)