求mysql left join 限制条数的语句
求一SQL select * from 文章表 left join 用户表 on 文章表.user_id = 用户表.id
现在我只想要每个用户对应的两篇文章这个要怎么写呢?
文章表数据
id user_id
1 2
2 2
3 2
4 2
5 2
6 3
7 3
8 3
9 3
想要的结果
id user_id
1 2
2 2
6 3
7 3
contents:文章表 user:用户表
-
SQL code
SELECT c1.*,u.*
FROM `contents` c1
LEFT JOIN `user` u ON c1.user_id=u.id
LEFT JOIN `contents` c2 ON c1.user_id=c2.user_id AND c1.id>c2.id
GROUP BY c1.id,c1.user_id,c1.content
HAVING COUNT(c2.id)<2
