我认为这里的困惑是您假设的值为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;
