小时/分钟------工作手册1-----技术手册2----------手册3---------------小计
张三 ---------0------------0:12--------------------------------0:12
李四 ---------0:32---------0:30------------0:05---------------1:07
赵六 ---------1:40---------0----------------0-----------------1:40
总计 ---------2:12---------0:42------------0:05---------------2:59比如张三看工作手册1有多条记录(每条记录有起始时间和结束时间) ,给个思路
张三 ---------0------------0:12--------------------------------0:12
李四 ---------0:32---------0:30------------0:05---------------1:07
赵六 ---------1:40---------0----------------0-----------------1:40
总计 ---------2:12---------0:42------------0:05---------------2:59比如张三看工作手册1有多条记录(每条记录有起始时间和结束时间) ,给个思路
with a as
(
select 1 as id,'kc1' as kc,60 as times from dual
union all
select 1 as id,'kc1' as kc,61 as times from dual
union all
select 1 as id,'kc2' as kc,62 as times from dual
union all
select 2 as id,'kc1' as kc,63 as times from dual
union all
select 2 as id,'kc2' as kc,64 as times from dual
union all
select 2 as id,'kc2' as kc,65 as times from dual
)
select id,sum(kc1) as kc1_times ,sum(kc2) as kc2_times from (
select id,decode(kc,'kc1',times,null) as kc1,decode(kc,'kc2',times,null) as kc2 from a)
group by id直接复制粘贴测试把
with a as
(
select 1 as id,'kc1' as kc,60 as times from dual
union all
select 1 as id,'kc1' as kc,61 as times from dual
union all
select 1 as id,'kc2' as kc,62 as times from dual
union all
select 2 as id,'kc1' as kc,63 as times from dual
union all
select 2 as id,'kc2' as kc,64 as times from dual
union all
select 2 as id,'kc2' as kc,65 as times from dual
)select id,
sum(decode(kc,'kc1',times,0)) kc1,
sum(decode(kc,'kc2',times,0)) kc2
from a
group by id
id kc1 kc2
-----------------------------------
1 1 121 62
2 2 63 129
sum(case when 课程 = '手册学习' then 时间 else 0 end)
sum(case when 课程 = '7NG培训' then 时间 else 0 end),
from tb_a group by 姓名
请楼主验证
MAX(DECODE(subject,'语文',theScore)) AS 语文,
MAX(DECODE(subject,'英语',theScore)) AS 英语 FROM name_subject_score GROUP BY theName ORDER BY theName额 很像吧