学习了一阵才终于搞定了,借鉴了项目长韩强飞之前的,终于一次性提取出来显示了,比以前快了不知道多少倍呢!
得好好学学SQL,sql写的好就是不一样!存储过程我记得以前写过的,但是现在怎么不会了!后面抽空得学回来!
select personname,
max(decode(Extract(day from kqrq), 1, property)) as day1,
max(decode(Extract(day from kqrq), 2, property)) as day2,
max(decode(Extract(day from kqrq), 3, property)) as day3,
max(decode(Extract(day from kqrq), 4, property)) as day4,
max(decode(Extract(day from kqrq), 5, property)) as day5,
max(decode(Extract(day from kqrq), 6, property)) as day6,
max(decode(Extract(day from kqrq), 7, property)) as day7,
max(decode(Extract(day from kqrq), 8, property)) as day8,
max(decode(Extract(day from kqrq), 9, property)) as day9,
max(decode(Extract(day from kqrq), 10, property)) as day10,
max(decode(Extract(day from kqrq), 11, property)) as day11,
max(decode(Extract(day from kqrq), 12, property)) as day12,
max(decode(Extract(day from kqrq), 13, property)) as day13,
max(decode(Extract(day from kqrq), 14, property)) as day14,
max(decode(Extract(day from kqrq), 15, property)) as day15,
max(decode(Extract(day from kqrq), 16, property)) as day16,
max(decode(Extract(day from kqrq), 17, property)) as day17,
max(decode(Extract(day from kqrq), 18, property)) as day18,
max(decode(Extract(day from kqrq), 19, property)) as day19,
max(decode(Extract(day from kqrq), 20, property)) as day20,
max(decode(Extract(day from kqrq), 21, property)) as day21,
max(decode(Extract(day from kqrq), 22, property)) as day22,
max(decode(Extract(day from kqrq), 23, property)) as day23,
max(decode(Extract(day from kqrq), 24, property)) as day24,
max(decode(Extract(day from kqrq), 25, property)) as day25,
max(decode(Extract(day from kqrq), 26, property)) as day26,
max(decode(Extract(day from kqrq), 27, property)) as day27,
max(decode(Extract(day from kqrq), 28, property)) as day28,
max(decode(Extract(day from kqrq), 29, property)) as day29,
max(decode(Extract(day from kqrq), 30, property)) as day30,
max(decode(Extract(day from kqrq), 31, property)) as day31
from (select personname, kqrq, property
from view_person_projAttend
where 1 = 1
and projid = '421440'
and kqrq BETWEEN to_date('2010-10-01', 'yyyy-MM-dd') AND
to_date('2010-10-31', 'yyyy-MM-dd'))
group by personname