求sql大神看看这个咋搞。sql分组排序的问题。

数据表 user

| id | name | time |

| 1 | aaa | 1 |

| 2 | bbb | 2 |

| 3 | ccc | 3 |

| 4 | aaa | 4 |

| 5 | ccc | 5 |

要求结果为：

| id | name | time |

| 5 | ccc | 5 |

| 3 | ccc | 3 |

| 4 | aaa | 4 |

| 1 | aaa | 1 |

| 3 | bbb | 3 |

需求是：
将最大的time对应的相同name全部置顶。
比方说最大的time是5，对应的name是ccc。就将所有name为ccc的排序在最前面。
然后第二个aaa的time为4，就将所有name为aaa的排在ccc的后面。

(select * from (
select * from
(user as u , (SELECT id,name, max(time) FROM user) mx)
WHERE u.id = mx.id
ORDER BY
u.time DESC) table1 )

UNION

(select * from
(SELECT * FROM
user as u
ORDER BY
u.time DESC) table2 )

楼主只完成了置顶ccc。剩余的就按时间倒序排序了。aaa那个怎么也想不出来咋做。求大神解答！！

1.优先按name列排序，然后是time列
2.name需要先把排序的顺序进行计算

try this

 select * from user order by find_in_set(name,
 (select group_concat(name order by time desc)  from 
 (select name,max(time) as time from user group by name) as tt),time desc;

select * from user where group by name order by time DESC

不行哎。这样ccc就只显示一条了。

select * from user group by name,time order by time DESC

mysql> select distinct t.* from ( select c2.* from users c1 left join users c2
on c1.name = c2.name order by c1.time desc ,c2.time desc ) t ;

SELECT

• ,(SELECT MAX(time) FROM FF_FF a WHERE a.name=t.NAME) AS mm FROM FF_FF t ORDER BY mm desc,t.time DESC,t.name

你想实现这种现实其实不需要分组，思想方向错了，其实单纯的order by就可以实现。
select * from user order by name desc,time desc;

按每个name最大的time排序

 SELECT * ,(SELECT MAX(time)  FROM user a WHERE a.name=t.NAME)  AS mm
FROM user t
ORDER BY mm desc,t.time DESC

id name time mm
5 ccc 5 5
3 ccc 3 5
4 aaa 4 4
1 aaa 1 4
2 bbb 2 2

应该是你要的效果吧。不要分组。

先用‘ROWCOUNT函数’查询出name的顺序作为表B
再用原来的表联查表B，用NAME对应，再根据表B的id进行排序就OK了
select a.*,b.id as bid from table a
left join
（select distinct name,rowcout as id from table order by time desc ）b
on a.name = b.name
order by bid
希望对你有帮助