您可以建立一个列表,您可以使用values()
子句将其外部连接到其中:
SELECT d.id, count(t.id) AS "count"
FROM (
values
('value1'),
('value2'),
('value3')
) as data (id)
left join the_table t on t.id = d.id
GROUP BY d.id
ORDER BY count ASC;
请注意,您必须使用count(t.id)
(对联接表中的值进行计数)来获得计数0
另外,如果您不想from
用values
子句弄乱部分,则可以使用公用表表达式:
with data (id) as (
values
('value1'),
('value2'),
('value3')
)
SELECT d.id, count(t.id) AS "count"
FROM data d
left join the_table t on t.id = d.id
GROUP BY d.id
ORDER BY count ASC;