case when a.recordTime >'2010-09-20 7:00:00' and a.recordTime < '2010-09-20 8:00:00' then '7至8点' when a.recordTime >'2010-09-20 8:00:00' and a.recordTime < '2010-09-20 9:00:00' then '8至9点' when a.recor
dTime >'2010-09-20 9:00:00' and a.recordTime < '2010-09-20 10:00:00' then '9至10点' when a.recordTime >'2010-09-20 10:00:00' and a.recordTime < '2010-09-20 11:00:00' then '10至11点' when a.recordTime >'2010-09-20 11:00:00' and a.recordTime < '2010-09-20 12:00:00' then '11至12点' when a.recordTime >'2010-09-20 12:
00:00' and a.recordTime < '2010-09-20 13:00:00' then '12至13点' end对这个case group by后,我想要的结果集是按 '7至8点' 、'8至9点' 、'9至10点' 、'11至12点' 、'12至13点' 排序的,请问应该如何做?(目前是按'11至12点' 、'12至13点'、 '7至8点' 、'8至9点' 、'9至10点' )解决立即揭帖
ps:'11至12点'都作为报表输出的一部分
dTime >'2010-09-20 9:00:00' and a.recordTime < '2010-09-20 10:00:00' then '9至10点' when a.recordTime >'2010-09-20 10:00:00' and a.recordTime < '2010-09-20 11:00:00' then '10至11点' when a.recordTime >'2010-09-20 11:00:00' and a.recordTime < '2010-09-20 12:00:00' then '11至12点' when a.recordTime >'2010-09-20 12:
00:00' and a.recordTime < '2010-09-20 13:00:00' then '12至13点' end对这个case group by后,我想要的结果集是按 '7至8点' 、'8至9点' 、'9至10点' 、'11至12点' 、'12至13点' 排序的,请问应该如何做?(目前是按'11至12点' 、'12至13点'、 '7至8点' 、'8至9点' 、'9至10点' )解决立即揭帖
ps:'11至12点'都作为报表输出的一部分
dTime >'2010-09-20 9:00:00' and a.recordTime < '2010-09-20 10:00:00' then '9至10点' when a.recordTime >'2010-09-20 10:00:00' and a.recordTime < '2010-09-20 11:00:00' then '10至11点' when a.recordTime >'2010-09-20 11:00:00' and a.recordTime < '2010-09-20 12:00:00' then '11至12点' when a.recordTime >'2010-09-20 12:
00:00' and a.recordTime < '2010-09-20 13:00:00' then '12至13点' end 字段名
完整的END后面应该有个字段名加个ORDER BY 字段名,是这个意思?
我目前想到的办法是:then 后写数字,然后在程序里循环结果集,把数字替换成报表显示的内容“7至8点”。。
但觉得太笨,所以请教,在sql有没有别的办法
select * from (case when ...end ordertime) t order by ordertime
case when a.recordTime >'2010-09-20 7:00:00' and a.recordTime < '2010-09-20 8:00:00' then '7至8点'
when a.recordTime >'2010-09-20 8:00:00' and a.recordTime < '2010-09-20 9:00:00' then '8至9点'
when a.recordTime >'2010-09-20 9:00:00' and a.recordTime < '2010-09-20 10:00:00' then '9至10点'
when a.recordTime >'2010-09-20 10:00:00' and a.recordTime < '2010-09-20 11:00:00' then '10至11点'
when a.recordTime >'2010-09-20 11:00:00' and a.recordTime < '2010-09-20 12:00:00' then '11至12点'
when a.recordTime >'2010-09-20 12:00:00' and a.recordTime < '2010-09-20 13:00:00' then '12至13点'
end 字段名
from t
order by
case when a.recordTime >'2010-09-20 7:00:00' and a.recordTime < '2010-09-20 8:00:00' then '1-7至8点'
when a.recordTime >'2010-09-20 8:00:00' and a.recordTime < '2010-09-20 9:00:00' then '2-8至9点'
when a.recordTime >'2010-09-20 9:00:00' and a.recordTime < '2010-09-20 10:00:00' then '3-9至10点'
when a.recordTime >'2010-09-20 10:00:00' and a.recordTime < '2010-09-20 11:00:00' then '4-10至11点'
when a.recordTime >'2010-09-20 11:00:00' and a.recordTime < '2010-09-20 12:00:00' then '5-11至12点'
when a.recordTime >'2010-09-20 12:00:00' and a.recordTime < '2010-09-20 13:00:00' then '6-12至13点'
end
那可以这样,decode(to_char(a.recordTime,'hh24'),'7','7至8点','8','7至8点','9','9至10点','10','9至10点',....)
你的时间字段用上面这个替代,试试
case when a.recordTime >'2010-09-20 7:00:00' and a.recordTime < '2010-09-20 8:00:00' then '1'
when a.recordTime >'2010-09-20 8:00:00' and a.recordTime < '2010-09-20 9:00:00' then '2'
when a.recordTime >'2010-09-20 9:00:00' and a.recordTime < '2010-09-20 10:00:00' then '3'
when a.recordTime >'2010-09-20 10:00:00' and a.recordTime < '2010-09-20 11:00:00' then '4'
when a.recordTime >'2010-09-20 11:00:00' and a.recordTime < '2010-09-20 12:00:00' then '5'
when a.recordTime >'2010-09-20 12:00:00' and a.recordTime < '2010-09-20 13:00:00' then '6'
end这样写也可以
目前的case语句就是完整的,执行效果是按照按'11至12点' 、'12至13点'、 '7至8点' 、'8至9点' 、'9至10点' 分组排序的,但我想要按照'7至8点' 、'8至9点' 、'9至10点' 、'11至12点' 、'12至13点'
那样的分组排序,不知道我表达清楚了没有?这个问题我已经找到解决答案了,谢谢各位