mysql实现row_number函数并获取所需要的行

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