954L
2017-04-28 06:52
采纳率: 100%
浏览 1.2k

求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那个怎么也想不出来咋做。求大神解答!!
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

9条回答 默认 最新

  • 欧阳嘉 2017-04-28 07:55
    已采纳

    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;
    
    打赏 评论
  • 75闪光雷 2017-04-28 06:53

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

    打赏 评论
  • 954L 2017-04-28 07:04

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

    打赏 评论
  • FEN_TA 2017-04-28 07:04

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

    打赏 评论
  • liquanchen9 2017-04-28 07:52

    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 ;

    打赏 评论
  • shl123s 2017-04-28 07:55

    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
    打赏 评论
  • 我是你波哥丶 2017-04-28 07:58

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

    打赏 评论
  • shl123s 2017-04-28 08:08

    按每个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

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

    打赏 评论
  • 欧尔迈特1994 2017-04-28 08:22

    先用‘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
    希望对你有帮助

    打赏 评论

相关推荐 更多相似问题