oracle多个sql语句合并成⼀条sql oracle多个sql语句合并成⼀条sql
(2012-04-12 09:53:38)
转载▼
标签：
oracle
多个sql
合并
分类：编程技术
⼀条
eqishare
luchao
it
oracle多个sql语句合并成⼀条sql
⼏个sql有相同的grp_id，需要把这⼏个sql合并成⼀条。
统计结果：
oracle多个sql语句合并成⼀条sql
sql如下：
select grp_id,sum(jcs) jcs,sum(jda) jda,sum(jwt) jwt,sum(jzl) jzl,sum(jqx) jqx,sum(ccs)
ccs,sum(cda) cda,sum(cwt) cwt,sum(czl) czl,sum(cqx) cqx from (
p_id ,count(*) jcs,0 as jda,0 as jwt,0 as jzl,0 as jqx,0 as ccs,0 as cda,0 as cwt,0 as czl,0 as cqx
from usersession us, c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
ame = cu.regname
and cgu.usr_id=cu.usr_id
and cui.usr_id=cu.usr_id
perttype = 10003
and us.sessionstart > to_date('2012-3-1', 'yyyy-mm-dd')
and us.sessionstart < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,count(*) jda,0 as jwt,0 as jzl,0 as jqx,0 as ccs,0 as cda,0 as cwt,0 as czl,0 as cqx
from ks_answer ka,c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
atorid=cu.usr_id
and cu.usr_id=cgu.usr_id
and cgu.usr_id=cui.usr_id
perttype=10003
ateddate > to_date('2012-3-1', 'yyyy-mm-dd')
ateddate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,count(*) jwt,0 as jzl,0 as jqx,0 as ccs,0 as cda,0 as cwt,0 as czl,0 as cqx
from ks_problem kp,c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
atorid=cu.usr_id
and cu.usr_id=cgu.usr_id
and cgu.usr_id=cui.usr_id
perttype=10003
ateddate > to_date('2012-3-1', 'yyyy-mm-dd')
ateddate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,count(*) jzl,0 as jqx,0 as ccs,0 as cda,0 as cwt,0 as czl,0 as cqx
from ks_document kd,c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
atorid=cu.usr_id
and cu.usr_id=cgu.usr_id
and cgu.usr_id=cui.usr_id
perttype=10003
atedate> to_date('2012-3-1', 'yyyy-mm-dd')
atedate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,0 as jzl,count(*) jqx,0 as ccs,0 as cda,0 as cwt,0 as czl,0 as cqx
from c_user_role cur,c_userotherinfo cui,c_goup_user cgu
where cur.usr_id=cui.usr_id
and cgu.usr_id=cui.usr_id
perttype=10003
ateddate > to_date('2012-3-1', 'yyyy-mm-dd')
ateddate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,0 as jzl,0 as jqx,count(*) ccs,0 as cda,0 as cwt,0 as czl,0 as cqx
from usersession us, c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
ame = cu.regname
and cgu.usr_id=cu.usr_id
and cui.usr_id=cu.usr_id
perttype = 10004
and us.sessionstart > to_date('2012-3-1', 'yyyy-mm-dd')
and us.sessionstart < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,0 as jzl,0 as jqx,0 as ccs,count(*) cda,0 as cwt,0 as czl,0 as cqx
from ks_answer ka,c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
atorid=cu.usr_id
and cu.usr_id=cgu.usr_id
and cgu.usr_id=cui.usr_id
perttype=10004
ateddate > to_date('2012-3-1', 'yyyy-mm-dd')
ateddate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,0 as jzl,0 as jqx,0 as ccs,0 as cda,count(*) cwt,0 as czl,0 as cqx
from ks_problem kp,c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
atorid=cu.usr_id
and cu.usr_id=cgu.usr_id
and cgu.usr_id=cui.usr_id
perttype=10004
ateddate > to_date('2012-3-1', 'yyyy-mm-dd')
ateddate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,0 as jzl,0 as jqx,0 as ccs,0 as cda,0 as cwt,count(*) czl,0 as cqx
from ks_document kd,c_user cu,c_goup_user cgu,C_USEROTHERINFO cui
atorid=cu.usr_id
and cu.usr_id=cgu.usr_id
and cgu.usr_id=cui.usr_id
perttype=10004
atedate> to_date('2012-3-1', 'yyyy-mm-dd')
atedate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id
union all
p_id,0 as jcs,0 as jda,0 as jwt,0 as jzl,0 as jqx,0 as ccs,0 as cda,0 as cwt,0 as czl,count(*) cqx
from c_user_role cur,c_userotherinfo cui,c_goup_user cgu
where cur.usr_id=cui.usr_id
and cgu.usr_id=cui.usr_id
perttype=10004
ateddate > to_date('2012-3-1', 'yyyy-mm-dd')
ateddate < to_date('2012-3-31', 'yyyy-mm-dd')
group p_id) group by grp_id;
我的更多⽂章：
Oracle的job学习使⽤及例⼦(2012-04-12 09:44:02)
oracle存储过程学习例⼦(2012-04-05 14:22:46)
Oracle存储过程循环特定次数和循环集合(2012-03-30 18:12:34)
Cannot load, the product "RM-697" is not recognised(2012-03-28 23:54:24)
ibatis传递数组参数(2012-03-27 12:03:47)
MyEclipse 8.6上搭建Android开发环境(2012-03-18 20:01:46)
Select statements cannot be executed as an update.(2012-03-14 17:02:00)
ibatis的cacheModel详解(2012-03-09 10:28:53)
Oracle查询 START WITH CONNECT BY PRIOR(2012-03-07 12:37:24)
oracle 字符串转数组java操作.csv⽂件⼯具类(2012-03-06 14:46:22)
分享：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。