将所有记录保存在"WHERE IN()"中子句，即使找不到 [英] Keep all records in "WHERE IN()" clause, even if they are not found

问题描述

我有以下mysql查询:

I have the following mysql query:

SELECT id, sum(views) as total_views
FROM table
WHERE id IN (1,2,3)
GROUP BY id
ORDER BY total_views ASC

如果在数据库中仅找到id 1,3，我仍然希望id 2出现，而total_views设置为0.

If only id 1,3 are found in the database, i still want id 2 to appear, with total_views being set to 0.

有没有办法做到这一点?这不能使用任何其他表.

Is there any way to do that? This cannot use any other table.

推荐答案

这里是Micheal解决方案的替代方案(只要您不是ID的很多"，就算是个不错的解决方案)，针对集群查询.

Here's an alternative to Micheal's solution (not a bad solution, mind you -- even with "a lot" of ID's), so long as you're not querying against a cluster.

create temporary table __ids (
  id int unsigned primary key
) engine=MEMORY;

insert into __ids (id) values
  (1),
  (2),
  (3)
;

SELECT table.id, sum(views) as total_views
FROM __ids left join table using (id)
GROUP BY table.id
ORDER BY total_views ASC

如果您的查询变得复杂，我什至可以想到它以这种方式更有效地运行.但是，如果我是您，我会使用真实数据通过Michael的临时UNION'ed表选项对这个选项进行基准测试.

And if your query becomes complex, I could even conceive of it running more efficiently this way. But, if I were you, I'd benchmark this option with Michael's ad-hoc UNION'ed table option using real data.

这篇关于将所有记录保存在"WHERE IN()"中子句，即使找不到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！