-- 大概这个样子 select fstr_loopgroupid, trunc(ftd_time) , sum(case to_char(ftd_time,'hh24mi' between '0000' and '0004' then fint_volumn else 0 end) C1 , sum(case to_char(ftd_time,'hh24mi' between '0005' and '0009' then fint_volumn else 0 end) C2 , .... sum(case to_char(ftd_time,'hh24mi' between '2355' and '2359' then fint_volumn else 0 end) C288,from t group by fstr_loopgroupid, trunc(ftd_time)
case when to_char(ftd_time,'hh24mi') between '0000' and '0004' then fint_volumn else 0 end
select fstr_loopgroupid, trunc(ftd_time) , sum(case to_char(ftd_time,'hh24mi' between '0000' and '0004' then fint_volumn else 0 end) C1 , sum(case to_char(ftd_time,'hh24mi' between '0005' and '0009' then fint_volumn else 0 end) C2 , .... sum(case to_char(ftd_time,'hh24mi' between '2355' and '2359' then fint_volumn else 0 end) C288,from t group by fstr_loopgroupid, trunc(ftd_time)
select fstr_loopgroupid, trunc(ftd_time) ,
sum(case to_char(ftd_time,'hh24mi' between '0000' and '0004' then fint_volumn else 0 end) C1 ,
sum(case to_char(ftd_time,'hh24mi' between '0005' and '0009' then fint_volumn else 0 end) C2 ,
....
sum(case to_char(ftd_time,'hh24mi' between '2355' and '2359' then fint_volumn else 0 end) C288,from t
group by fstr_loopgroupid, trunc(ftd_time)
可以动态生成 这 12 * 24 个列语句;执行的时候,拼接成 一个大的 SQL ;
可以动态生成 这 12 * 24 个列语句;执行的时候,拼接成 一个大的 SQL ; 报错为缺失右括号,我在'hh24mi'后面补上了还是报这个错,是不是to_char不能作变量表达式?
sum(case to_char(ftd_time,'hh24mi' between '0000' and '0004' then fint_volumn else 0 end) C1 ,
sum(case to_char(ftd_time,'hh24mi' between '0005' and '0009' then fint_volumn else 0 end) C2 ,
....
sum(case to_char(ftd_time,'hh24mi' between '2355' and '2359' then fint_volumn else 0 end) C288,from t
group by fstr_loopgroupid, trunc(ftd_time)