有两个表:subject表和attachfile表,之间是一对多的关系,也就是一个主题里可以后多个附件,表结构举例如下:
subject表:
s_id int(10),
publish_date date.attachfile表:
a_id int(10),
s_id int(10),
content varchar(200).现在我的需求是要搜出最近发表的6条subject,并且一并取出来它的一条附件。sql如下:
SELECT * FROM subject s LEFT JOIN attachfile a ON s.s_id=a.s_id ORDER BY a.PUBLISH_DATE DESC但是如果一个subject中有多条附件时就会查出多行,我只想一条subject跟随属于它的多条附件中的一条,怎么办?
subject表:
s_id int(10),
publish_date date.attachfile表:
a_id int(10),
s_id int(10),
content varchar(200).现在我的需求是要搜出最近发表的6条subject,并且一并取出来它的一条附件。sql如下:
SELECT * FROM subject s LEFT JOIN attachfile a ON s.s_id=a.s_id ORDER BY a.PUBLISH_DATE DESC但是如果一个subject中有多条附件时就会查出多行,我只想一条subject跟随属于它的多条附件中的一条,怎么办?
from(
select a.*,b.*,row_number() over(partition by a.s_id order by b.a_id) b_rn,row_number() over(order by publish_date desc) a_rn
from subject a,attchfile b
where a.s_id=b.s_id(+))
where a._rn<=6 and b_rn=1
select a.*,b.*,row_number() over(partition by a.s_id order by b.a_id) b_rn,row_number() over(order by publish_date desc) a_rn 中row_number() 和两个over的意思
分组排序
第二个A_RN按时间降序产生subject的编号,最后只取最近的6条
SELECT * FROM subject s LEFT JOIN attachfile a ON s.s_id=a.s_id
where (a.a_id in(select min(a_id) from attachfile where s_id=a.s_id) or a.a_id is null)
order by s.publish_date desc;不过还是学到了不少。谢谢。