解决方案 »
- oracle适用用语句收藏,欢迎补充..
- 事务管理。急!
- oracle建分区时能否只按时间不要日期限制??
- 数据库恢复
- 多主体复制:帮助中说可以用Replication Management API在不静默主题站点的情况下来实现增加主体站点,请问如何实现?
- 求ORACLE APEX中文使用说明
- PL/SQL Developer怎么调用包呢,语法怎么写~~
- 我的存储过程里需要解压缩.tar文件,怎么办?
- 访问量特大!数据库选型及解决方案彷徨中......... [有实施经验者请进]
- 数据导入导出问题
- 急求:请各位仁兄帮帮看看这个SQL怎么回事?在Ibatis里始终没法通过,而在PL/SQL中单独执行完全没有问题。
- 一个比较复杂的问题
t.name1,
t.content,
t.deductDescn,
a.score as cbsscore,
b.score as jlscore,
c.score as yzscore
from (Select cgat.id as itemId,
Case
When cgat.code = 'GCZZ' Then
'一'
Else
Case
When cgat.code = 'GCZL' Then
'二'
Else
Case
When cgat.code = 'GCJD' Then
'三'
Else
Case
When cgat.code = 'AQWM' Then
'四'
Else
cgat.code
End
End
End
End bh1,
Name || '(' || cgat.score || ')' As name1,
Name || '(' || cgat.score || ')' As content,
Name || '(' || cgat.score || ')' As Deductdescn,
Name || '(' || cgat.score || ')' As name4,
Name || '(' || cgat.score || ')' As contentCode,
Name || '(' || cgat.score || ')' As name6,
treecode
From ip_ct_goodJob_assessType cgat
Where length(treeCode) = 5
and name <> '加分因素'
And contractType = 'XMHT_SG_HT'
union
select cgai.id as itemId,
cgat1.code as bh2,
cgat1.name || '(' || cgat1.score || ')' as name2,
cgai.content as content,
cgai.Deductdescn as Deductdescn,
cgai.contentCode,
'' name3,
'' name4,
cgat1.treecode || '000' || cgai.contentCode as treecode
from ip_ct_goodJob_assessItem cgai,
ip_ct_goodJob_assessType cgat1,
ip_ct_goodJob_assessType cgat2
where cgai.assessTypeId = cgat1.id
and cgai.activity = 1
and cgat2.activity = 1
and cgat1.contracttype = 'XMHT_SG_HT'
and cgat2.contracttype = 'XMHT_SG_HT'
and cgat1.parentid = cgat2.id
and cgat1.name <> '加分因素') t
left join (select cgjd.itemid, cgjd.score
from ip_ct_goodJob cgj, ip_ct_goodJob_detail cgjd
where cgj.id = cgjd.goodjobid
and cgj.postid = 'contractor'
and cgj.id = ''
and cgj.contractid = '') a
on t.itemId = a.itemid
left join (select cgjd.itemid, cgjd.score
from ip_ct_goodJob cgj, ip_ct_goodJob_detail cgjd
where cgj.id = cgjd.goodjobid
and cgj.postid = 'supervisor'
and cgj.id = ''
and cgj.contractid = '') b
on t.itemId = b.itemid
left join (select cgjd.itemid, cgjd.score
from ip_ct_goodJob cgj, ip_ct_goodJob_detail cgjd
where cgj.id = cgjd.goodjobid
and cgj.postid = 'owner'
and cgj.id = ''
and cgj.contractid = '') c
on t.itemId = a.itemid
union
select '扣分总计= ' as kfzj1,
'扣分总计= ' as kfzj2,
'扣分总计= ' as kfzj3,
'扣分总计= ' as kfzj4,
cbs.cbsscore,
jl.jlscore,
yz.yzscore
from (select cg.id, sum(cgd.score) cbsscore
from ip_ct_goodJob_assessType cgat
left join ip_ct_goodJob_assessItem cgai
on cgat.id = cgai.Assesstypeid
left join ip_ct_goodJob_detail cgd
on cgd.itemId = cgai.id
left join ip_ct_goodJob cg
on cg.id = cgd.goodjobid
where cgat.contracttype = 'XMHT_SG_HT'
and cgat.activity = 1
and cgai.activity = 1
--and cg.contractid=''
--and cg.id=''
and cg.postid = 'contractor'
group by cg.id) cbs
left join (select cg.id, sum(cgd.score) as jlscore
from ip_ct_goodJob_assessType cgat
left join ip_ct_goodJob_assessItem cgai
on cgat.id = cgai.Assesstypeid
left join ip_ct_goodJob_detail cgd
on cgd.itemId = cgai.id
left join ip_ct_goodJob cg
on cg.id = cgd.goodjobid
where cgat.contracttype = 'XMHT_SG_HT'
and cgat.activity = 1
and cgai.activity = 1
--and cg.contractid=''
--and cg.id=''
and cg.postid = 'supervisor'
group by cg.id) jl
on cbs.id = jl.id
left join (select cg.id, sum(cgd.score) as yzscore
from ip_ct_goodJob_assessType cgat
left join ip_ct_goodJob_assessItem cgai
on cgat.id = cgai.Assesstypeid
left join ip_ct_goodJob_detail cgd
on cgd.itemId = cgai.id
left join ip_ct_goodJob cg
on cg.id = cgd.goodjobid
where cgat.contracttype = 'XMHT_SG_HT'
and cgat.activity = 1
and cgai.activity = 1
--and cg.contractid=''
--and cg.id=''
and cg.postid = 'owner'
group by cg.id) yz
on cbs.id = yz.id