MCA DAILY_KEY STUTAS TIMES
BL1 20080812 DOWN 1214.48
BL1 20080812 RUN 1269.35
BL1 20080813 DOWN 343.05
BL1 20080813 RUN 1003.93
输出结果:MCA DAILY_KEY RUN_TIMES END_TIMES
BL1 20080812 1269.35 1214.48
BL1 20080813 1003.93 343.05
BL1 20080812 DOWN 1214.48
BL1 20080812 RUN 1269.35
BL1 20080813 DOWN 343.05
BL1 20080813 RUN 1003.93
输出结果:MCA DAILY_KEY RUN_TIMES END_TIMES
BL1 20080812 1269.35 1214.48
BL1 20080813 1003.93 343.05
select MCA,DAILY_KEY,max(decode(STUTAS,'RUN',TIMES)) RUN_TIMES,max(decode(STUTAS,'DOWN',TIMES)) END_TIMES,
max(decode(STUTAS,'DOWN',TIMES))||'/'||max(decode(STUTAS,'RUN',TIMES)) "DOWN/RUN"
from tb group by MCA,DAILY_KEY order by 1,2;
DAILY_KEY,
max(decode(STUTAS,'DOWN',STUTAS,null)) RUN_TIMES ,
max(decode(STUTAS,'RUN',STUTAS,null)) END_TIMES ,
from yourtable
group by DAILY_KEY
select distinct MCA
DAILY_KEY,
sum(decode(STATUS,'RUN',TIMES,0)) over(partition by MCA,DAILY) RUN_TIMES,
sum(decode(STATUS,'DOWN',TIMES,0)) over(partition by MCA,DAILY) END_TIMES
from TABLE_NAME;
select mca,daily_key,
max(decode(stutas,'RUN',times))run_times,
max(decode(stutas,'DOWN',times))end_times,
max(decode(stutas,'DOWN',times))||'/'||max(decode(stutas,'RUN',times)) "down/rum"
from table1
group by mca,daily_key
17:01:56 2 select 'BL1' MCA,'20080812' DAILY_KEY,'DOWN' STUTAS,'1214.48' TIMES from dual
17:01:56 3 union all
17:01:56 4 select 'BL1','20080812','RUN','1269.35' from dual
17:01:56 5 union all
17:01:56 6 select 'BL1','20080813','DOWN','343.05' from dual
17:01:56 7 union all
17:01:56 8 select 'BL1','20080813','RUN','1003.93' from dual
17:01:56 9 union all
17:01:56 10 select 'BL2','20080813','DOWN','343.05' from dual
17:01:56 11 union all
17:01:56 12 select 'BL2','20080813','RUN','1003.93' from dual)
17:01:56 13 select MCA,DAILY_KEY,max(decode(STUTAS,'RUN',TIMES)) RUN_TIMES,max(decode(STUTAS,'DOWN',TIMES)) END_TIMES,
17:01:56 14 max(decode(STUTAS,'DOWN',TIMES))||'/'||max(decode(STUTAS,'RUN',TIMES)) "DOWN/RUN"
17:01:56 15 from tb group by MCA,DAILY_KEY order by 1,2;MCA DAILY_KE RUN_TIM END_TIM DOWN/RUN
--- -------- ------- ------- ---------------
BL1 20080812 1269.35 1214.48 1214.48/1269.35
BL1 20080813 1003.93 343.05 343.05/1003.93
BL2 20080813 1003.93 343.05 343.05/1003.93已用时间: 00: 00: 00.01
group by MCA,DAILY_KEY.
不知道对不对。
FROM(
select MCA,
DAILY_KEY,
max(decode(STUTAS,'DOWN',TIMES,null)) RUN_TIMES ,
max(decode(STUTAS,'RUN',TIMES,null)) END_TIMES ,
from yourtable
group by MCA,DAILY_KEY
)
已经解决了,谢谢大家,还是有case when 方便。
oracle里面的decode好像就是case when
有的人喜欢用CASE WHEN
有的用DECODE()
习惯而已
建议使用CASE WHEN
可能case when 很多数据库都支持吧,
另外一个可能就是读起来比较方便些
就跟写代码的时候喜欢if else if else 不喜欢 ?:?:一样。