模拟数据

CREATE TABLE `t_break` (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `area_id` varchar(20) NOT NULL,
  `user_id` varchar(20) NOT NULL,
  `break_type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('A', '1', 'b1');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('A', '1', 'b2');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('A', '1', 'b3');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('C', '1', 'b2');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('C', '1', 'b3');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('B', '2', 'b1');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('B', '2', 'b3');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('B', '2', 'b4');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('A', '3', 'b2');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('B', '3', 'b3');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('C', '3', 'b1');
INSERT INTO t_break (`area_id`, `user_id`,  `break_type`) VALUES ('C', '3', 'b2');

数据如下：

根据area_id 和user_id分组，为每个分组的记录从后到前添加序号

SELECT *,
(SELECT count(id) FROM t_break AS A
where A.area_id=B.area_id AND 
A.user_id=B.user_id AND B.id<A.id
)as sn
from t_break AS B

查询结果如下：

获取每个人在每个城区最近2条违法记录

SELECT * FROM t_break B WHERE 
(SELECT count(id) FROM t_break AS A
where A.area_id=B.area_id AND 
A.user_id=B.user_id AND B.id<A.id
)<2

查询结果如下：

另外也可用如下sql进行查询，查询结果和上图查询结果相同，但数据量过大时，执行效率较慢

SELECT A.id, A.area_id, A.user_id,COUNT(B.id) AS count FROM t_break AS A 
LEFT JOIN t_break B ON B.area_id = A.area_id AND B.user_id = A.user_id 
AND B.id > A.id GROUP BY A.id,A.area_id,A.user_id HAVING count < 2