Home  >  Article  >  Backend Development  >  sql关于相同父ID最多取3记录

sql关于相同父ID最多取3记录

WBOY
WBOYOriginal
2016-06-23 14:08:55990browse

表 imginfo
id    fid    miaoshu
1      3     aaaaaaa
2      3     sssssss
3      3     fffff
4      4     uuuuuuuu
5      5     gfgfgfgfgf
6      5     ddfdfdfd
7      9     fdffdfd
8      9     dfdfdfd
9      9     dfdfdf
10     9     popopop
11     3     tgtrtyrtyry
12     5      fdsjfdfdl

SELECT * FROM `imginfo` WHERE fid in(3,5,9) ORDER BY `id` DESC 
上面这条语句fid 3,5,9全部内容都获取了。

现在我只想每条相同fid最多获取3条,排序不变,sql语句怎么写? 
请大家指教请高人指教。不胜感激! 


回复讨论(解决方案)

你SQL取出来在php那端每个值取3个不就结了?

拍完序之后仅获取前三条的话可以用limit0,3;


select a.* from imginfo a where 3 > (select count(*) from imginfo where fid = a.fid and id > a.id and fid in(3,5,9)) and fid in(3,5,9) order by a.fid,a.id

SQL语句如下:
select a.* from imginfo a where 3 > (select count(*) from imginfo where fid = a.fid and id > a.id and fid in(3,5,9)) and fid in(3,5,9) order by a.fid,a.id

如果只是按ID降序,排序改下就好
select a.* from imginfo a where 3 > (select count(*) from imginfo where fid = a.fid and id > a.id and fid in(3,5,9)) and fid in(3,5,9) order by  a.id desc

可以这样的哦,学习了。

学习了!谢谢版主。


select a.* from imginfo a where 3 > (select count(*) from imginfo where fid = a.fid and id > a.id and fid in(3,5,9)) and fid in(3,5,9) order by a.fid,a.id

版主 想问下 我想按照条数的多少来排序该怎么做呢?比如fid 为3的有5条 为9的有10条 为5的有3条 排序就为 9 3 5  请问怎么实现了 谢谢了

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