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

Question

例如:select case when a = '1' then 'a1' 
            when a = '2' then 'a2' 
            else  'aa' end ,count(*)
from table
group by case when a = '1' then 'a1' 
              when a = '2' then 'a2' 
              else  'aa' end
这样要是a='1'这个条件没有数据符合的话,根本不会显示'a1'行,有什么办法解决吗?

Answer 1

就一个简单的表id  a
1   2
2   2
3   2
4   3
5   2用上面的语句查出来的是case  count(*)
 a2     4
 aa     1
这样少了a1了,我想要的效果是case  count(*)
 a1     0
 a2     4
 aa     1

Answer 2

--建表
create table t_test
(
idx int,
val int
)
--伪造数据
begin
  for i in 1..5 loop
    insert into t_test values (i,i / 2);
  end loop;
end;delete from t_test where val = 1;
commit;
--执行查询select a.casefield ,nvl(b.counted,0) as countField  from 
(
SELECT 'a' || ROWNUM as caseField FROM dual 
  CONNECT BY ROWNUM <= 6--要显示几行就写几
) a left join 
(select 'a' || val as caseField,count(1) as counted from t_test
group by val
) b on a.casefield = b.caseField
order by a.casefield--结果
1 a1 0
2 a2 2
3 a3 1
4 a4 0
5 a5 0
6 a6 0

Answer 3

可以再建一个表把要统计的都放在一个表里面，然后左连接 上面的结果表；select a.a_name,nvl(b.count_num,0) as count_num from table2 a left join (select (case when a = '1' then 'a1' 
            when a = '2' then 'a2' 
            else  'aa' end ) as a_name ,count(*) as count_num 
from table
group by case when a = '1' then 'a1' 
              when a = '2' then 'a2' 
              else  'aa' end ) t on a.a_name=t.a_name ;)

Answer 4

虽然大家的方法都可行,我也很感谢大家,但大家的方法都有些钻空子了.这里的a1,a2是我为了方便描述而举例的,其实实则为时间,是获取系统时间,精确到分钟,而每一行是系统时间减5分钟或15分钟之类的,所以无法固定这里的数据,实在不能利用row来取到同样的名字钻空子啊.还有就是我并不是直接操作数据库的,所以那种建临时表的方法也不可行,只能用select语句.再次求大家想想办法啦.

Answer 5

你这个查询量会非常大的。我仅用了一天的数据进行查询都要写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')
--结果，这个是我表里的值
4 2011-11-29 11:00:00 2
5 2011-11-29 11:30:00 1
6 2011-11-29 14:45:00 1
7 2011-11-29 15:15:00 1--延用上面的思路，先伪造每隔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--最终结果
132 2011-11-29 10:55:00 0
133 2011-11-29 11:00:00 2
134 2011-11-29 11:05:00 0
135 2011-11-29 11:10:00 0
136 2011-11-29 11:15:00 0
137 2011-11-29 11:20:00 0
138 2011-11-29 11:25:00 0
139 2011-11-29 11:30:00 1
140 2011-11-29 11:35:00 0
141 2011-11-29 11:40:00 0
142 2011-11-29 11:45:00 0
143 2011-11-29 11:50:00 0
144 2011-11-29 11:55:00 0
145 2011-11-29 12:00:00 0
146 2011-11-29 12:05:00 0
147 2011-11-29 12:10:00 0
148 2011-11-29 12:15:00 0
149 2011-11-29 12:20:00 0
150 2011-11-29 12:25:00 0
151 2011-11-29 12:30:00 0
152 2011-11-29 12:35:00 0
153 2011-11-29 12:40:00 0
154 2011-11-29 12:45:00 0
155 2011-11-29 12:50:00 0
156 2011-11-29 12:55:00 0
157 2011-11-29 13:00:00 0
158 2011-11-29 13:05:00 0
159 2011-11-29 13:10:00 0
160 2011-11-29 13:15:00 0
161 2011-11-29 13:20:00 0
162 2011-11-29 13:25:00 0
163 2011-11-29 13:30:00 0
164 2011-11-29 13:35:00 0
165 2011-11-29 13:40:00 0
166 2011-11-29 13:45:00 0
167 2011-11-29 13:50:00 0
168 2011-11-29 13:55:00 0
169 2011-11-29 14:00:00 0
170 2011-11-29 14:05:00 0
171 2011-11-29 14:10:00 0
172 2011-11-29 14:15:00 0
173 2011-11-29 14:20:00 0
174 2011-11-29 14:25:00 0
175 2011-11-29 14:30:00 0
176 2011-11-29 14:35:00 0
177 2011-11-29 14:40:00 0
178 2011-11-29 14:45:00 1
179 2011-11-29 14:50:00 0
180 2011-11-29 14:55:00 0
181 2011-11-29 15:00:00 0
182 2011-11-29 15:05:00 0
183 2011-11-29 15:10:00 0
184 2011-11-29 15:15:00 1

Answer 6

9楼的方法不错,只不过我那时间并不是00:00:00开始每隔5分钟.我的是系统获得当前时间,然后再每次减去一个周期时间,这周期是5分钟,15分钟.....不定的.但只要把9楼的方法改为系统当前时间,然后每次减去周期*row就行了.还是谢谢大家.