Question

用SQL写Oracle翻页的时候提醒我未明确定义列。。

select * from

(
select a.*,c.xm,d.*,rownum as num from hust_xs_jb a
left join HUST_XX_CLASS d on d.bjbh=a.bjbh
inner join HUST_XX_IDENTITY b on b.sfid=a.sfid
inner join HUST_XX_PROFILE c on b.uuid=c.uuid
where a.sxlbm
in('25')
and (a.xjzt in('01','18','60') or a.xjzt is null) and a.bjbh is not null and d.nj is not null
and (c.hkyszss is null and c.gjm in ('156') ) or (c.gjm is null and c.hkyszss is null)

)
where num >= '50' and num <= '100'

中间括号内的那段语句单独可执行，没有问题。

是怎么回事呢

Answer 1

因为你括号里的有*，可能是有重名的字段，括号外加个表的别名就好,如果括号内的*里有叫num的字段也不可以，要换个别名
select AA.* from

(
select a.*,c.xm,d.*,rownum as num from hust_xs_jb a
left join HUST_XX_CLASS d on d.bjbh=a.bjbh
inner join HUST_XX_IDENTITY b on b.sfid=a.sfid
inner join HUST_XX_PROFILE c on b.uuid=c.uuid
where a.sxlbm
in('25')
and (a.xjzt in('01','18','60') or a.xjzt is null) and a.bjbh is not null and d.nj is not null
and (c.hkyszss is null and c.gjm in ('156') ) or (c.gjm is null and c.hkyszss is null)

) AA
where AA.num >= '50' and AA.num <= '100'

Answer 2

楼主 看看执行以下这个 应该没问题:
select * from

(
select a.*,(c.xm) as xm2,d.*,rownum as num from hust_xs_jb a
left join HUST_XX_CLASS d on d.bjbh=a.bjbh
inner join HUST_XX_IDENTITY b on b.sfid=a.sfid
inner join HUST_XX_PROFILE c on b.uuid=c.uuid
where a.sxlbm
in('25')
and (a.xjzt in('01','18','60') or a.xjzt is null) and a.bjbh is not null and d.nj is not null
and (c.hkyszss is null and c.gjm in ('156') ) or (c.gjm is null and c.hkyszss is null)

)
where num >= '50' and num <= '100'

a 与 b 表中有相同名的字段 请区别开就没问题了~

Answer 3

where num >= '50' and num <= '100 '
num 未明确定义!
select * from

(
select a.*,c.xm,d.*,rownum as num from hust_xs_jb a
left join HUST_XX_CLASS d on d.bjbh=a.bjbh
inner join HUST_XX_IDENTITY b on b.sfid=a.sfid
inner join HUST_XX_PROFILE c on b.uuid=c.uuid
where a.sxlbm
in('25')
and (a.xjzt in('01','18','60') or a.xjzt is null) and a.bjbh is not null and d.nj is not null
and (c.hkyszss is null and c.gjm in ('156') ) or (c.gjm is null and c.hkyszss is null)

) n
where n.num >= '50' and n.num <= '100 '

Answer 4

select * from

(
select a.*,c.xm,d.*,rownum as num from hust_xs_jb a
left join HUST_XX_CLASS d on d.bjbh=a.bjbh
inner join HUST_XX_IDENTITY b on b.sfid=a.sfid
inner join HUST_XX_PROFILE c on b.uuid=c.uuid
where a.sxlbm
in('25')
and (a.xjzt in('01','18','60') or a.xjzt is null) and a.bjbh is not null and d.nj is not null
and (c.hkyszss is null and c.gjm in ('156') ) or (c.gjm is null and c.hkyszss is null)

) E
where num >= '50' and num <= '100 '

Answer 5

括号内的查出来的字段重名了