这是我项目中用到的 表有几张 有点乱哈 大概就是这样 哈哈哈.......
如下是一个子查询 里面先排序 将结果集给一个别名user_satellite_info 在通过use_id排序
SELECT * from (
SELECT * FROM user_satellite_info where user_id in
(SELECT praise_user_id from public_praise_info WHERE friend_circle_dynamic_id=91 AND is_praise=1 and user_type=0)
order BY upload_time DESC
) AS user_satellite_info GROUP BY user_id
使用left join 看如何做 我这个用于取头像 之前头像有多张 查询出来 得到每一条数据的头像是最早的那张 不是我想要的 所以 对头像排个序然后 在分组 在排序
SELECT c.*,us.nick_name,u.upload_time,u.thumbnail_path,u.thumbnail_file_name ,
pu.public_nick_name,pu.head_portrait_thumbnail_file_name,pu.head_portrait_thumbnail_path
from public_friend_circle_comment c
LEFT JOIN user_info us ON us.user_id=c.user_id
LEFT JOIN public_user_info pu ON pu.c_user_id=c.user_id
LEFT JOIN (SELECT user_id, upload_time, thumbnail_path, thumbnail_file_name from user_satellite_info ORDER BY upload_time ASC)
AS u ON c.user_id=u.user_id
WHERE friend_circle_dynamicId=91 group BY c.friend_circle_comment_id ORDER BY c.comment_time ASC LIMIT 0,100;