我SQL SERVER中的语句是这样写的:
select a.*,
(select zmmc from dic_zmbm where zmbm = (select top 1 ddz from dat_bdsj_bzxx where xtrq = a.xtrq and zh = a.zh and bdh = a.bdh order by bzcs desc)) ddzm,
(select dqdmc from dic_dqdm where dqdm = a.dqdm) dqmc,bzcs from dat_bdsj_bzxx a
where xtrq = '2014-11-01' and zh = 'as' and bdh = 44 and czgl > 1
如何改成ORACLE?
我写成
select a.*,
(select zmmc from dic_zmbm where zmbm = (select ddz from dat_bdsj_bzxx where xtrq = a.xtrq and zh = a.zh and bdh = a.bdh and rownum = 1 order by bzcs desc)) ddzm,
(select dqdmc from dic_dqdm where dqdm = a.dqdm) dqmc,bzcs from dat_bdsj_bzxx a
where xtrq = '2014-11-01' and zh = 'as' and bdh = 44 and czgl > 1
报错..如果把里面的子查询先排序再FROM也不行..也就是:
select a.*,
(select zmmc from dic_zmbm where zmbm = (select ddz from (select * from dat_bdsj_bzxx where xtrq = a.xtrq and zh = a.zh and bdh = a.bdh and rownum = 1 order by bzcs desc) as tb where rownum = 1)) ddzm,
(select dqdmc from dic_dqdm where dqdm = a.dqdm) dqmc,bzcs from dat_bdsj_bzxx a
where xtrq = '2014-11-01' and zh = 'as' and bdh = 44 and czgl > 1
这样内部子查询内需要再次排序如何写SQL语句?
------解决思路----------------------
分析函数的写法吧，这个效率应该是最好的
select a.*,
(select zmmc from dic_zmbm where zmbm = (
select max(ddz)keep(dense_rank last order by bzcs) from dat_bdsj_bzxx
where xtrq = a.xtrq and zh = a.zh and bdh = a.bdh
)
) ddzm,
(select dqdmc from dic_dqdm where dqdm = a.dqdm) dqmc,bzcs
from dat_bdsj_bzxx a
where xtrq = '2014-11-01' and zh = 'as' and bdh = 44 and czgl > 1