3,491
社区成员
发帖
与我相关
我的任务
分享
select
a.bill_sn 工单编号,
c.operate_end_time 操作时间,
(SELECT E.USERNAME
FROM JS_IDA.BAF_ORG_USER E
WHERE E.USERID = c.agentor) 操作人人,
(select t8.objectname
from JS_IDA.BAF_ORG_TREE t8
where t8.objectid=c.agentor_group) 操作人部门,
c.remark 操作备注
from js_ida.t_pub_mainbill_his a,
js_ida.t_open_billinfo_his b,
js_ida.t_pub_bill_action_his c
where a.bill_id=b.bill_id
and a.bill_id=c.bill_id
and a.revert_time>=to_date('2011-04-28','YYYY-MM-DD')
and a.revert_time<(to_date('2011-04-30','YYYY-MM-DD')+1)
第一类:如果K_NAME项目固定,使用DEOCDE函数
with test as(
select 3 USER_ID,'背景知识' K_NAME, 4.5 SCORE from dual union all
select 3 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all
select 3 USER_ID,'服务意识' K_NAME, 1 SCORE from dual union all
select 3 USER_ID,'沟通能力' K_NAME, 1 SCORE from dual union all
select 4 USER_ID,'背景知识' K_NAME, 12 SCORE from dual union all
select 4 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all
select 4 USER_ID,'服务意识' K_NAME, 0.5 SCORE from dual union all
select 4 USER_ID,'沟通能力' K_NAME, 0.5 SCORE from dual)
SELECT USER_ID,
SUM(decode(K_NAME, '背景知识', SCORE)) "背景知识",
SUM(decode(K_NAME, '创新能力', SCORE)) "创新能力",
SUM(decode(K_NAME, '服务意识', SCORE)) "服务意识",
SUM(decode(K_NAME, '沟通能力', SCORE)) "沟通能力"
FROM test
GROUP BY USER_ID;
第二类:如果项数不固定,使用存储过程动态SQL拼接
表结构如下:
t_fl:
lb
----
a
b
c
d
e
t_sj
id lb sl
-------------
1 a 10
1 c 30
1 d 40
1 e 50
2 b 20
2 e 50
想弄出这样一个结果:
id sl_a sl_b sl_c sl_d sl_e
---------------------------------
1 10 30 40 50
2 20 50
--1、如果t_fl表行数据不定的话可以写一个存储过程
CREATE OR REPLACE PROCEDURE row2col(o OUT SYS_REFCURSOR) IS
sqlstr VARCHAR2(4000) := '';
BEGIN
FOR cc IN (SELECT lb FROM t_fl) LOOP
sqlstr := sqlstr || 'sum(decode(lb,''' || cc.lb ||''',sl)) as "sl_'
|| cc.lb || '",';
END LOOP;
sqlstr := 'select id,' || rtrim(sqlstr,',') || ' from t_sj group by id';
OPEN o FOR sqlstr;
END row2col;