问个case when符合条件为空也显示一列的有关问题

问个case when符合条件为空也显示一列的问题
例如:

SQL code

select case when a = '1' then 'a1'             when a = '2' then 'a2'             else  'aa' end ,count(*)from tablegroup by case when a = '1' then 'a1'               when a = '2' then 'a2'               else  'aa' end

这样要是a='1'这个条件没有数据符合的话,根本不会显示'a1'行,有什么办法解决吗?

[解决办法]
实测成功：


[解决办法]
你这个查询量会非常大的。我仅用了一天的数据进行查询都要写N多代码。

--将时间值进行转化，不足5的，补齐为5
select ceil(to_char(sysdate,'mi')/5)*5 from dual

--首先日期字段每隔5分钟进行格式化，因为你没给表结构，我就直接用我自己的表了。
select to_date(to_char(acqdate, 'yyyy-mm-dd hh24') || ':' ||
decode(ceil(to_char(acqdate, 'mi') / 5) * 5,
60,
0,
ceil(to_char(acqdate, 'mi') / 5) * 5) || ':00',
'yyyy-mm-dd hh24:mi:ss') as recordTime,
count(1) as counted
from hi_dataacquisition
 group by to_date(to_char(acqdate, 'yyyy-mm-dd hh24') || ':' ||
decode(ceil(to_char(acqdate, 'mi') / 5) * 5,
60,
0,
ceil(to_char(acqdate, 'mi') / 5) * 5) || ':00',
'yyyy-mm-dd hh24:mi:ss')
--结果，这个是我表里的值
42011-11-29 11:00:002
52011-11-29 11:30:001
62011-11-29 14:45:001
72011-11-29 15:15:001

--延用上面的思路，先伪造每隔5分钟的记录，以一天为例
SELECT to_date('2011-11-29 ' || to_char(ceil((ROWNUM) * 5 / 60) - 1) || ':' ||
to_char(mod((ROWNUM - 1) * 5, 60)) || ':00',
'yyyy-mm-dd hh24:mi:ss') as service_date
FROM dual
CONNECT BY ROWNUM <= 288


--复杂的查询
select service_date,nvl(counted,0) from 
(SELECT to_date('2011-11-29 ' || to_char(ceil((ROWNUM) * 5 / 60) - 1) || ':' ||
to_char(mod((ROWNUM - 1) * 5, 60)) || ':00',
'yyyy-mm-dd hh24:mi:ss') as service_date
FROM dual
CONNECT BY ROWNUM <= 288) a
left join 
( select to_date(to_char(acqdate, 'yyyy-mm-dd hh24') || ':' ||
decode(ceil(to_char(acqdate, 'mi') / 5) * 5,
60,
0,
ceil(to_char(acqdate, 'mi') / 5) * 5) || ':00',
'yyyy-mm-dd hh24:mi:ss') as recordTime,
count(1) as counted
from hi_dataacquisition
 group by to_date(to_char(acqdate, 'yyyy-mm-dd hh24') || ':' ||
decode(ceil(to_char(acqdate, 'mi') / 5) * 5,
60,
0,
ceil(to_char(acqdate, 'mi') / 5) * 5) || ':00',
'yyyy-mm-dd hh24:mi:ss')
) b on a.service_date = b.recordTime 
order by a.service_date

--最终结果
1322011-11-29 10:55:000
1332011-11-29 11:00:002
1342011-11-29 11:05:000
1352011-11-29 11:10:000
1362011-11-29 11:15:000
1372011-11-29 11:20:000
1382011-11-29 11:25:000
1392011-11-29 11:30:001
1402011-11-29 11:35:000
1412011-11-29 11:40:000
1422011-11-29 11:45:000
1432011-11-29 11:50:000
1442011-11-29 11:55:000
1452011-11-29 12:00:000
1462011-11-29 12:05:000
1472011-11-29 12:10:000
1482011-11-29 12:15:000
1492011-11-29 12:20:000
1502011-11-29 12:25:000
1512011-11-29 12:30:000
1522011-11-29 12:35:000
1532011-11-29 12:40:000
1542011-11-29 12:45:000
1552011-11-29 12:50:000
1562011-11-29 12:55:000
1572011-11-29 13:00:000
1582011-11-29 13:05:000
1592011-11-29 13:10:000
1602011-11-29 13:15:000
1612011-11-29 13:20:000
1622011-11-29 13:25:000
1632011-11-29 13:30:000
1642011-11-29 13:35:000
1652011-11-29 13:40:000
1662011-11-29 13:45:000
1672011-11-29 13:50:000
1682011-11-29 13:55:000
1692011-11-29 14:00:000
1702011-11-29 14:05:000
1712011-11-29 14:10:000
1722011-11-29 14:15:000
1732011-11-29 14:20:000
1742011-11-29 14:25:000
1752011-11-29 14:30:000
1762011-11-29 14:35:000
1772011-11-29 14:40:000
1782011-11-29 14:45:001
1792011-11-29 14:50:000
1802011-11-29 14:55:000
1812011-11-29 15:00:000
1822011-11-29 15:05:000
1832011-11-29 15:10:000
1842011-11-29 15:15:001