数据库题:(oracle)
主贴表topic:id,title,createdate(主键、题目、发布日期)
从贴表subject:id,tid,content,createdate(主键、所属主贴、内容、发布日期)
要求:用一条sql语句查出主贴表中最新的十条记录,并查出该记录最新的一条从贴。记住:一条SQL
高手们给个答案吧
主贴表topic:id,title,createdate(主键、题目、发布日期)
从贴表subject:id,tid,content,createdate(主键、所属主贴、内容、发布日期)
要求:用一条sql语句查出主贴表中最新的十条记录,并查出该记录最新的一条从贴。记住:一条SQL
高手们给个答案吧
union
select t.tid id , t.content title , t.createdate from subject t where createdate = (select max(createdate) from subject where tid = t.tid) and tid in (select top 10 id from topic where rownum <= 10 order by createdate desc)
order by id , createdate
好像要两个表的结构一样吧,不知道楼主两个表之间有什么关系
select tid max (createdate) from subject order by tid 最新的一条最新的一条从贴
你看看怎么跟另外一个表关联
(
select id,title,createdate,row_number() over(order by createdate desc) rn
from topic
) aa
where aa.rn<=10
union all
select tid,ID,content,createdate from
(
select id,tid,content,createdate,row_number() over(partition by tid order by createdate desc) rn
from subject
) bb
where bb.rn<=1
and bb.tid in
(
select id from
(
select id,row_number() over(order by createdate desc) rn
from topic
) aa
where aa.rn<=10
)
order by 1,2 desc
/