SELECT *
FROM (SELECT page.*, ROWNUM AS rn
FROM ((select p.id,
p.plate,
p.device_id,
p.platecolor_code,
p.platetype_code,
p.cartype_code,
p.carcolor_code,
p.catchspeed,
p.catchtime,
ty.code,
ty.name,
e.resource_id,
e.type,
re.local,
re.remote,
re.absolute,
r.name,
rd.name,
rd.lane_number
from EA_ILLEGAL_PASS p
left join EA_ILLEGAL_TYPE ty
on ty.code = p.illegaltype_id
left join EA_ILLEGAL_EVIDENCE e
on e.illegal_id = p.illegaltype_id
left join EA_PUBLIC_RESOURCE re
on re.id = e.resource_id
left join EA_PUBLIC_DEVICE d
on d.id = p.device_id
left join EA_PUBLIC_ROAD r
on r.id = d.road_id
left join EA_PUBLIC_ROAD_DIRECTION rd
on rd.road_id = d.road_id
where e.type = 'F') UNION
(select rej.id,
rej.plate,
rej.device_id,
rej.platecolor_code,
rej.platetype_code,
rej.cartype_code,
rej.carcolor_code,
rej.catchspeed,
rej.catchtime,
ty2.code,
ty2.name,
e2.resource_id,
e2.type,
re2.local,
re2.remote,
re2.absolute,
r2.name,
rd2.name,
rd2.lane_number
from EA_ILLEGAL_REJECT rej
left join EA_ILLEGAL_TYPE ty2
on ty2.code = rej.illegaltype_id
left join EA_ILLEGAL_EVIDENCE e2
on e2.illegal_id = rej.illegaltype_id
left join EA_PUBLIC_RESOURCE re2
on re2.id = e2.resource_id
left join EA_PUBLIC_DEVICE d2
on d2.id = rej.device_id
left join EA_PUBLIC_ROAD r2
on r2.id = d2.road_id
left join EA_PUBLIC_ROAD_DIRECTION rd2
on rd2.road_id = d2.road_id
where e2.type = 'F')) page)
WHERE rn BETWEEN 1 AND 20
报错为：
Cause: java.sql.SQLSyntaxErrorException: ORA-00918: 未明确定义列
我就纳闷了，究竟哪里有问题了，单独执行一下的SQL是正常的
(select p.id,
p.plate,
p.device_id,
p.platecolor_code,
p.platetype_code,
p.cartype_code,
p.carcolor_code,
p.catchspeed,
p.catchtime,
ty.code,
ty.name,
e.resource_id,
e.type,
re.local,
re.remote,
re.absolute,
r.name,
rd.name,
rd.lane_number
from EA_ILLEGAL_PASS p
left join EA_ILLEGAL_TYPE ty
on ty.code = p.illegaltype_id
left join EA_ILLEGAL_EVIDENCE e
on e.illegal_id = p.illegaltype_id
left join EA_PUBLIC_RESOURCE re
on re.id = e.resource_id
left join EA_PUBLIC_DEVICE d
on d.id = p.device_id
left join EA_PUBLIC_ROAD r
on r.id = d.road_id
left join EA_PUBLIC_ROAD_DIRECTION rd
on rd.road_id = d.road_id
where e.type = 'F') UNION
(select rej.id,
rej.plate,
rej.device_id,
rej.platecolor_code,
rej.platetype_code,
rej.cartype_code,
rej.carcolor_code,
rej.catchspeed,
rej.catchtime,
ty2.code,
ty2.name,
e2.resource_id,
e2.type,
re2.local,
re2.remote,
re2.absolute,
r2.name,
rd2.name,
rd2.lane_number
from EA_ILLEGAL_REJECT rej
left join EA_ILLEGAL_TYPE ty2
on ty2.code = rej.illegaltype_id
left join EA_ILLEGAL_EVIDENCE e2
on e2.illegal_id = rej.illegaltype_id
left join EA_PUBLIC_RESOURCE re2
on re2.id = e2.resource_id
left join EA_PUBLIC_DEVICE d2
on d2.id = rej.device_id
left join EA_PUBLIC_ROAD r2
on r2.id = d2.road_id
left join EA_PUBLIC_ROAD_DIRECTION rd2
on rd2.road_id = d2.road_id
where e2.type = 'F')
麻烦各位帮我瞧瞧，剩下最后20分了
------解决思路----------------------
Union里的列名与主select里的列名重复了也不行，是吧？
------解决思路----------------------
看了下楼主的SQL,你单独能运行,说明问题出现在分页那块,不知道你数据库是什么,另外你是在同一张表里面进行查询，只条件不同，不用那么复杂的，用IN替代赛！
1.你尝试把别名的AS去掉试试
2.可能是你查询结果列名重复，试试给字段都加上别名。
这只是我看代码臆测的，希望能帮到楼主!