mysql使用心得
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `staff`
-- ----------------------------
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` smallint(10) DEFAULT NULL,
`department` int(10) DEFAULT NULL,
`type` smallint(5) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of staff
-- ----------------------------
INSERT INTO `staff` VALUES ('1', '刘德华', '54', '1', '1', '2014-06-21 11:29:22');
INSERT INTO `staff` VALUES ('2', '张学友', '50', '1', '1', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('3', '郭富城', '52', '1', '1', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('4', '黎明', '53', '1', '1', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('5', '刘德华', '54', '2', '2', '2014-06-21 11:39:27');
INSERT INTO `staff` VALUES ('6', '梁朝伟', '55', '2', '2', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('7', '黄日华', '57', '2', '2', '2014-06-21 11:29:27');
INSERT INTO `staff` VALUES ('8', '梁朝伟', '55', '3', '3', '2014-06-21 11:30:36');
INSERT INTO `staff` VALUES ('9', '刘德华', '54', '3', '3', '2014-06-21 11:31:01');
SELECT * FROM `staff` as t1 group by `name`,age,department,type,create_time
having create_time = (select max(create_time) from staff as t2 where t2.name=t1.name ) #group by t2.name
;
解析：group by是先按`name`,age,department,type,create_time 进行分组，分完组后having对每个分组里面按照create_time进行过滤，最后得到每个name的最新一条记 录；也就行有多少个分组分组就要执行多少遍 having create_time = (select max(create_time) from staff as t2 where t2.name=t1.name ) 语句，select max(create_time) from staff as t2 where t2.name=t1.name 查询的数据源是从全表中通过name进行过滤后，再查找最大的一条。
思考：可以把having后面的所有create_time都换成id看看结果对比一下
另一种通过子查询的实现方式：
SELECT * FROM
(
select * from staff order by create_time desc
) as t1 group by `name`;
bitsCN.com