求个sql语句

码拜

9年 ago

有如下表结构：
picid   uid   field1   field2 …
1           9527
2           9528
3           9528
4           9527
…
也就是每个用户对应N张图片，现在需要找出指定的每个用户的前4张图片，求这个sql语句，多谢！

解决方案

select * from table where uid = “” order by picid limit 4

select *
from tb A
where 4>=(select count(*) from tb B where A.uid=B.uid and A.uid<B.uid)

SELECT *
FROM 
(
SELECT *,
       (SELECT COUNT(*) FROM TB T2 WHERE T1.UID = T2.UID AND T1.PICID  <= T2.PICID) RN
FROM TB T1
)T
WHERE RN <= 4

select * from (
select *,row_number over(partition by uid order by picid asc) as idd from table where uid in (9527,9528…)
where idd <=4;

select *
from tb A
where 4>=(select count(*) from tb B where A.uid=B.uid and A.picid<B.picid)

参考下贴中的多种方法
http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集，及散分….