1.创建测试表
CREATE TABLE `contacts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
2.导入数据
insert into `contacts`(`id`,`first_name`,`last_name`,`email`) values (1,'Carine ','Schmitt','carine.schmitt@qq.com'),(2,'Jean','King','jean.king@yiibai.com'),(3,'Peter','Ferguson','peter.ferguson@google.com'),(4,'Janine ','Labrune','janine.labrune@aol.com'),(5,'Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),(6,'Janine ','Labrune','janine.labrune@aol.com'),(7,'Susan','Nelson','susan.nelson@qq.com'),(8,'Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@qq.com'),(9,'Roland','Keitel','roland.keitel@yahoo.com'),(10,'Julie','Murphy','julie.murphy@yahoo.com'),(11,'Kwai','Lee','kwai.lee@google.com'),(12,'Jean','King','jean.king@qq.com'),(13,'Susan','Nelson','susan.nelson@qq.comt'),(14,'Roland','Keitel','roland.keitel@yahoo.com');
3.查询row_number值为1的用户,并且id值是最大的
select a.num,a.id,a.CustomerNumber,a.last_name,a.email from (SELECT
@row_number:=CASE
WHEN @customer_no = first_name THEN @row_number + 1
ELSE 1
END AS num,
id,
@customer_no:=first_name as CustomerNumber,
last_name,
email
FROM
contacts
ORDER BY CustomerNumber,id desc) a
join
(SELECT
@row_number:=CASE
WHEN @customer_no = first_name THEN @row_number + 1
ELSE 1
END AS num,
id,
@customer_no:=first_name as CustomerNumber,
last_name,
email
FROM
contacts
ORDER BY CustomerNumber,id desc) b
on ( a.CustomerNumber=b.CustomerNumber and a.num=b.num)
where a.num=1