select语句始终在执行时返回3行 [英] select statement to always return 3 rows on execution

问题描述

我有一条select语句，该语句始终仅返回3行.一列的静态值为RED AMBER和GREEN.

i have a select statement which always returns only 3 rows. One column has static values RED AMBER and GREEN.

http://sqlfiddle.com/#!6/5e107/2

在小提琴链接中，我有一个示例代码.

In the fiddle link, i have a sample code.

 select color,SUM(clrcount) from 
 tmp group by color 

在执行时返回3行. 但是，如果我删除标记为琥珀色的项目，然后进行分组，则仅返回两行.我需要3行才能始终返回. AMBER行的计数可以为0

returns 3 rows on execution. But if i delete items marked AMBER, and then do a group by, only two rows are returned. I need 3 rows to be always returned. The AMBER row can have count as 0

推荐答案

重新设计是一种更好的方法，但是如果您现在无法确定这三种颜色，请尝试以下方法:

Redesigning would be a better approach but if you can't do it now and certain about those three colours, try something like this :

小提琴演示

Fiddle demo

select t.color,coalesce(sum(clrcount),0) colorCount
from tmp  right join 
         (values('RED'),('AMBER'),('GREEN')) As t(color)
     on tmp.color = t.color
group by t.color

这篇关于select语句始终在执行时返回3行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！