select ta.*, tc.content,tc.createTime from (select * from (select * from t_article order by createTime) where rownum <= 10) ta left join (select aritcleid, content, createTime from (select aritcleid, content, createTime, row_number() over(partition by aritcleid order by createTime) as rn from t_comment) where rn <= 3) tc on ta.id = tc.aritcleid
create table t_article( id number, name varchar2(100), createTime date default sysdate );drop table t_comment; CREATE TABLE t_comment( articleid number, content varchar(100), createTime date default sysdate );INSERT INTO t_article SELECT LEVEL, 'Name' || to_char(LEVEL), SYSDATE - LEVEL FROM dual CONNECT BY LEVEL <= 20; INSERT INTO t_comment SELECT mod(LEVEL,20), 'Hello' || to_char(LEVEL), SYSDATE - mod(LEVEL, 20) FROM dual CONNECT BY LEVEL <= 80; COMMIT;SELECT * FROM t_article order by createtime; SELECT * FROM t_comment order by createtime;WITH article AS ( SELECT * FROM (SELECT id,name, createtime FROM t_article ar ORDER BY createTime ASC ) WHERE rownum <= 10 ), article_comments AS ( SELECT ar.id, ar.name, ar.createtime ar_create_time, tco.articleid, tco.content, tco.createtime tco_create_time, row_number() over(partition by ar.id order by tco.createtime) rn FROM article ar LEFT OUTER JOIN t_comment tco ON ar.id=tco.articleid ) SELECT * FROM article_comments where rn <= 3; 用了外连接,考虑到有些文章没评论。
select ta.*, tc.content,tc.createTime
from (select *
from (select * from t_article order by createTime)
where rownum <= 10) ta
left join (select aritcleid, content, createTime
from (select aritcleid,
content,
createTime,
row_number() over(partition by aritcleid
order by createTime) as rn
from t_comment)
where rn <= 3) tc
on ta.id = tc.aritcleid
create table t_article(
id number,
name varchar2(100),
createTime date default sysdate
);drop table t_comment;
CREATE TABLE t_comment(
articleid number,
content varchar(100),
createTime date default sysdate
);INSERT INTO t_article SELECT LEVEL, 'Name' || to_char(LEVEL), SYSDATE - LEVEL FROM dual CONNECT BY LEVEL <= 20;
INSERT INTO t_comment SELECT mod(LEVEL,20), 'Hello' || to_char(LEVEL), SYSDATE - mod(LEVEL, 20) FROM dual CONNECT BY LEVEL <= 80;
COMMIT;SELECT * FROM t_article order by createtime;
SELECT * FROM t_comment order by createtime;WITH article AS (
SELECT * FROM (SELECT id,name, createtime FROM t_article ar ORDER BY createTime ASC ) WHERE rownum <= 10
),
article_comments AS (
SELECT ar.id, ar.name, ar.createtime ar_create_time, tco.articleid, tco.content, tco.createtime tco_create_time, row_number() over(partition by ar.id order by tco.createtime) rn
FROM article ar LEFT OUTER JOIN t_comment tco ON ar.id=tco.articleid
)
SELECT * FROM article_comments where rn <= 3;
用了外连接,考虑到有些文章没评论。