mysql使用心得

bitsCN.com

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