将所有记录保存在"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屋!