wu_0916
竖心旁的情
采纳率100%
2017-08-24 01:39 阅读 5.9k

mysql先排序再分组再排序

3

原数据:图片说明
想要的结果:
y 10-01
y 08-10
y 08-07
r 09-01
r 08-25
r 08-07
ss 08-18
ss 08-07

先对他们按时间排序,再分组,再对组内的数据按时间进行排序

建表语句:
DROP TABLE IF EXISTS testor;
CREATE TABLE testor (
id int(11) NOT NULL,
name varchar(255) DEFAULT NULL,
crdate datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- Records of testor


INSERT INTO testor VALUES ('0', 'ss', '2017-08-18 09:07:42');
INSERT INTO testor VALUES ('1', 'y', '2017-08-10 09:07:57');
INSERT INTO testor VALUES ('2', 'r', '2017-08-07 09:08:06');
INSERT INTO testor VALUES ('3', 'r', '2017-08-25 09:08:19');
INSERT INTO testor VALUES ('4', 'r', '2017-09-01 09:08:33');
INSERT INTO testor VALUES ('5', 'ss', '2017-08-07 09:08:53');
INSERT INTO testor VALUES ('6', 'y', '2017-08-07 11:51:12');
INSERT INTO testor VALUES ('7', 'y', '2017-10-01 11:51:23');

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

10条回答 默认 最新

  • 已采纳
    tingyk 村西头最帅的仔 2017-08-24 04:50

    select t.name,t.crdate
    from testor t
    left join(
    select * from(
    select name, crdate
    from testor
    order by crdate desc
    )q group by q.name
    ) o on t.name = o.name
    order by o.crdate desc,t.name,t.crdate desc;

    点赞 1 评论 复制链接分享
  • Amo_lt Amo_lt 2017-08-24 07:20

    模仿已采纳的那个答案,进行了一点修改。☺
    select t.name,t.crdate
    from testor t
    left join(
    select name,max(crdate) as tr from(
    select name, crdate
    from testor
    order by crdate desc
    )q group by q.name
    )o on t.name = o.name
    order by o.tr desc,t.name,t.crdate desc;

    点赞 1 评论 复制链接分享
  • zy_281870667 Bug开发攻城狮 2017-08-24 02:05

    select * from (select * from table order by time)t group by name order by time

    点赞 评论 复制链接分享
  • wu_0916 竖心旁的情 2017-08-24 02:21

    select * from (select * from table order by time)t group by name order by time
    这个不行
    图片说明
    只有3条数据,没有进行组内排序

    点赞 评论 复制链接分享
  • u014046563 shy078 2017-08-24 02:23

    select name, right(left(crdate,10),5) as time from 表名 order by crdate desc group by name

    点赞 评论 复制链接分享
  • wu_0916 竖心旁的情 2017-08-24 02:30

    select name, right(left(crdate,10),5) as time from 表名 order by crdate desc group by name

    order by crdate desc group by name 这写法是不对的

    点赞 评论 复制链接分享
  • Amo_lt Amo_lt 2017-08-24 03:20

    楼主把表格的sql语句发出来下,我来试下,嘿嘿,不一定能成功喔

    点赞 评论 复制链接分享
  • wu_0916 竖心旁的情 2017-08-24 03:43

    DROP TABLE IF EXISTS testor;
    CREATE TABLE testor (
    id int(11) NOT NULL,
    name varchar(255) DEFAULT NULL,
    crdate datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    -- Records of testor


    INSERT INTO testor VALUES ('0', 'ss', '2017-08-18 09:07:42');
    INSERT INTO testor VALUES ('1', 'y', '2017-08-10 09:07:57');
    INSERT INTO testor VALUES ('2', 'r', '2017-08-07 09:08:06');
    INSERT INTO testor VALUES ('3', 'r', '2017-08-25 09:08:19');
    INSERT INTO testor VALUES ('4', 'r', '2017-09-01 09:08:33');
    INSERT INTO testor VALUES ('5', 'ss', '2017-08-07 09:08:53');

    点赞 评论 复制链接分享
  • wu_0916 竖心旁的情 2017-08-24 04:13

    根据你想要的结果看,是不需要分组的,进行两次排序就可以了吧
    select name, date_format(crdate,'%m-%d') as time from 表名 order by name, crdate desc

    图片说明

    点赞 1 评论 复制链接分享
  • tingyk 村西头最帅的仔 2017-08-24 03:31

    根据你想要的结果看,是不需要分组的,进行两次排序就可以了吧
    select name, date_format(crdate,'%m-%d') as time from 表名 order by name, crdate desc

    点赞 评论 复制链接分享

相关推荐