Home  >  Article  >  Database  >  MySQL查询同个表中不同分类的前几条记录_MySQL

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

WBOY
WBOYOriginal
2016-06-01 13:40:351304browse

bitsCN.com
场景:
一个文章表,存放有关PHP的文章,和MySQL的文章,想从这些文章类别各自提取N条记录出来。
 测试表语句:CREATETABLEarticle(
idINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,
cateVARCHAR(20),
titleVARCHAR(20)
);

INSERTINTOarticle(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);
 /
 方案一:SELECTa.id,a.title,a.cate
FROMarticleASaLEFTJOINarticleASbONa.cate=b.cateANDa.idGROUPBYa.id,a.title,a.cate
HAVINGCOUNT(b.id)ORDERBYa.idDESC; 方案二:SELECT*FROMarticleASa
WHERE5>(SELECTCOUNT(*)FROMarticleWHEREcate=a.cateANDid>a.id)
ORDERBYa.idDESC; /
  bitsCN.com

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn