MySQL查询同个表中不同分类的前几条记录

2019独角兽企业重金招聘Python工程师标准>>>

场景：
一个文章表，存放有关PHP的文章，和MySQL的文章，想从这些文章类别各自提取N条记录出来。

测试表语句：

CREATE TABLE article(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cate VARCHAR(20),
title VARCHAR(20)
);
INSERT INTO article(cate, title) VALUES
('php', 'php文章1'),
('mysql', 'mysql文章1'),
('php', 'php文章2'),
('mysql', 'mysql文章2'),
('php', 'php文章3'),
('mysql', 'mysql文章3'),
('php', 'php文章4'),
('mysql', 'mysql文章4'),
('php', 'php文章5'),
('mysql', 'mysql文章5'),
('php', 'php文章6'),
('mysql', 'mysql文章6'),
('php', 'php文章7'),
('mysql', 'mysql文章7'),
('php', 'php文章8'),
('mysql', 'mysql文章8');

方案一：

SELECT a .id , a .title , a .cate
FROM article AS a LEFT JOIN article AS b ON a .cate = b .cate AND a .id < b .id
GROUP BY a .id , a .title , a .cate
HAVING COUNT (b .id ) < 5
ORDER BY a .id DESC;

 

方案二：

SELECT * FROM article AS a
WHERE 5 > ( SELECT COUNT ( * ) FROM article WHERE cate = a .cate AND id > a .id )
ORDER BY a .id DESC;

 

转载于:https://my.oschina.net/robinlin/blog/78941