如何使用“ in”运算符返回0而不是null

Question

我有三张桌子：

1. 文字：行中的文字

2. trigram：所有文字行的trigram

3. text_trigram：文本行包含的三字母组，中间表

当我执行此命令时：

select count(coalesce(text_id,0)), text_id 
from text_trigram 
where text_id in (1, 2, 3) 
and trigram_id = 1 
group by text_id;

结果出来了，却没有null我想要的结果0：

count|text_id
1       1 
1       2

这是我除了拥有的东西：

count|text_id 
 1       1 
 1       2
 0       3

此外，我想执行以下操作：

select count(coalesce(text_id,0)), text_id 
from text_trigram 
where text_id in (1, 2, 3) 
and trigram_id in (1, 2, 3) 
group by text_id;



count|text_id|trigram_id 
   1       1        1
   1       1        2
   0       1        3
   1       2        1
   1       2        2
   1       2        3
   0       3        1

有可能的？还是使用in运算符是错误的？

Answer 1

我认为这里的困惑是您假设的值为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_id
group by required_text_ids.text_id, required_trigram_ids.trigram_id
order by required_text_ids.text_id, required_trigram_ids.trigram_id;