sql实现分组查询

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');

 

 1 SELECT
 2     t.id,
 3     t. NAME,
 4     t.crdate,
 5     t.phone
 6 FROM
 7     testor t
 8 LEFT JOIN (
 9     SELECT
10         NAME,
11         max(crdate) AS tr
12     FROM
13         (
14             SELECT
15                 NAME,
16                 crdate
17             FROM
18                 testor
19             ORDER BY
20                 crdate DESC
21         ) q
22     GROUP BY
23         q. NAME
24 ) o ON t. NAME = o. NAME
25 ORDER BY
26     o.tr DESC,
27     t. NAME,
28     t.crdate DESC;

 

转载于:https://www.cnblogs.com/every-smile/p/9963181.html