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 ,) gt ON st.id = gt.name_id';
上一篇:jQuery解决iframe高度自适应代码


下一篇:使用jquery获取父元素或父节点的方法