select t1.mrl_id, t1.mrl_name, t1.mrl_about, t2.jd_img from (select mrl_id, mrl_name, mrl_about from ( select mrl_id, mrl_name, mrl_about, row_number() over(order by create_time desc) rn from Memorial where top_id = 0 ) where rn <= 3 ) t1, (select bigclass_id, jd_img from ( select bigclass_id, jd_img, row_number() over(partition by bigclass_id order by jd_img) rn from jd_imgmove t2 ) where rn = 1 ) t2 where t1.mrl_id = t2.bigclass_id;
select m.mrl_id,m.mrl_name, m.mrl_about,n.jd_id,n.bigclass_id,n.jd_img from (select * from (select a.mrl_id,a.mrl_name, a.mrl_about from Memorial a where top_id = 0 order by create_time desc) where rownum <= 3) m, (select c.jd_id,c.bigclass_id,c.jd_img, row_number()over(partition by bigclass_id order by jd_id) rn from jd_imgmove c ) n where n.rn = 1 m.mrl_id = n.jd_id;
试着执行了一下,提示“'row_number' 不是可以识别的 函数名。”
最后一行错了应该是m.mrl_id = n.bigclass_id
select a.mrl_id,a.mrl_name,mrl_about,b1.jd_img from a, (select bigclass_id,jd_img,row_number()over(partition by bigclass_id order by rownum)rn from b)b1 where a.mrl_id=b1.bigclass_id and b.rn=1 and rownum<=3
from
(select mrl_id, mrl_name, mrl_about
from (
select mrl_id, mrl_name, mrl_about, row_number() over(order by create_time desc) rn
from Memorial
where top_id = 0
)
where rn <= 3
) t1,
(select bigclass_id, jd_img
from (
select bigclass_id, jd_img, row_number() over(partition by bigclass_id order by jd_img) rn
from jd_imgmove t2
)
where rn = 1
) t2
where t1.mrl_id = t2.bigclass_id;
select m.mrl_id,m.mrl_name, m.mrl_about,n.jd_id,n.bigclass_id,n.jd_img
from (select *
from (select a.mrl_id,a.mrl_name, a.mrl_about
from Memorial a
where top_id = 0
order by create_time desc)
where rownum <= 3) m,
(select c.jd_id,c.bigclass_id,c.jd_img,
row_number()over(partition by bigclass_id order by jd_id) rn
from jd_imgmove c
) n
where n.rn = 1
m.mrl_id = n.jd_id;
from a,
(select bigclass_id,jd_img,row_number()over(partition by bigclass_id order by rownum)rn
from b)b1
where a.mrl_id=b1.bigclass_id
and b.rn=1
and rownum<=3