oracle 查询两段数据Union不了。。出现什么未正确结束 求高手指导。。。
本帖最后由 CaoYYYY 于 2013-06-06 08:56:11 编辑
select t.bh1Oracle select
,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
order by treecode
--union
--下面这是另外一部分 查询出来的字段也是一样 分开运行的话 他就能出数据 我考虑过 是不是 order by 的问题。。。不过 我放到后面 还是一样 我把 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
[解决办法]
1 两句要都能正确执行
2 查询字段数量和类型必须一致
[解决办法]
order by 只能在union all 完成后的末尾使用，去掉 第一个查询的order by 这是常见错误