mysql多表查询及其 group by 组内排序

 

//多表查询：得到最新的数据后再执行多表查询

SELECT *FROM `students` `st` RIGHT JOIN(
  SELECT * FROM 
　　(
　　　　SELECT * FROM goutong WHERE goutongs='asdf' ORDER BY time DESC
　　) AS gtt GROUP BY gtt.name_id ORDER BY gtt.goutong_time DESC ) gt 
　　ON `gt`.`name_id`=`st`.`id` LIMIT 10

 

//先按时间排序查询，然后分组(GROUP BY ) 
SELECT * FROM 
　　(
　　　　SELECT * FROM goutong WHERE goutongs='asdf' ORDER BY time DESC　　) AS gtt GROUP BY gtt.name_id ORDER BY gtt.time DESC

 

 

参考：http://blog.csdn.net/shellching/article/details/8292338

有数据表 comments
------------------------------------------------
| id | newsID | comment | theTime |
------------------------------------------------
| 1  |        1      |         aaa    |     11       |
------------------------------------------------
| 2  |        1      |         bbb    |     12       |
------------------------------------------------
| 3  |        2      |         ccc     |     12       |

------------------------------------------------

newsID是新闻ID，每条新闻有多条评论comment，theTime是发表评论的时间

现在想要查看每条新闻的最新一条评论：

select * from comments group by newsID 显然不行

select * from comments group by newsID order by theTime desc 是组外排序，也不行

下面有两种方法可以实现：

（1）
selet tt.id,tt.newsID,tt.comment,tt.theTime from(  
select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID 

（2）
select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having
 theTime=(select max(theTime) from comments where newsID=tt.newsID)

补充： 通过最大时间 然后再联合查询出其它信息，实现避免分组排序的问题。（多个子查询实现功能）

 

        'SELECT gt.time,  gt.name_id,  gt.goutong,gt.operator, st.id,st.Stu_name,st.Stu_sex,st.stu_gongsi,st.stu_waishangke,st.Stu_jjcourse,st.Stu_phone,st.Stu_beizhu FROM jingjie_students AS st RIGHT JOIN (SELECT A.* FROM jingjie_goutong A, (SELECT name_id,MAX(goutong_time) goutong_time FROM jingjie_goutong WHERE  operator = '小明' GROUP BY name_id) B WHERE A.name_id = B.name_id AND A.time = B.time  ORDER BY A.time DESC LIMIT 0,10) gt ON st.id = gt.name_id';