SQL about the same parent ID, a maximum of three records table imginfo can be obtained.
Id fid miaoshu
1 3 aaaaaaa
2 3 sssssss
3 3 fffff
4 4 uuuuuuuuu
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
All content of the above statements fid, 5, and 9 is obtained.
Now I only want to get up to three identical FIDS, and the sorting remains unchanged. how can I write SQL statements?
Please kindly advise. Thank you!
Reply to discussion (solution)
If you obtain the SQL statement and get three values for each value in the php end, it will not end?
If you get only the first three items after the order, you can use 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
The SQL statement is as follows:
Select. * from imginfo a where 3> (select count (*) from imginfo where fid =. fid and id>. id and fid in (3,5, 9) and fid in (3,5, 9) order by. fid,. id
If you only sort by ID in descending order, sort and change it.
Select. * from imginfo a where 3> (select count (*) from imginfo where fid =. fid and id>. id and fid in (3,5, 9) and fid in (3,5, 9) order by. id desc
You can.
Learning! Thank you, moderator.
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
The moderator would like to ask how to sort by the number of entries? For example, if the fid is 3 and there are 5 results 9 and 10 results 5 and 3 orders 9 and 3 5, how can this problem be achieved? thank you!