统计用户初次领证后三年(非自然年)内，每年各级违法各有多少次。

考点：SUBSTR()、TIMESTAMPDIFF（）

题目：统计用户初次领证后三年(非自然年)内，每年各级违法各有多少次。

1.建表：

CREATE TABLE IF NOT EXISTS `illegal_1`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `user_id` INT NOT NULL,
   `cclzrq` DATE NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `illegal_2`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `user_id` INT NOT NULL,
   `vio_level` VARCHAR(5) NOT NULL,
	 `vio_time` DATETIME NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据：

insert into illegal_1 (user_id, cclzrq) values('10023','2015-04-21');
insert into illegal_1 (user_id, cclzrq) values('15674','1998-05-12');
insert into illegal_1 (user_id, cclzrq) values('45345','2008-03-19');
insert into illegal_1 (user_id, cclzrq) values('87945','2004-08-26');
insert into illegal_1 (user_id, cclzrq) values('12355','2018-06-08');

insert into illegal_2 (user_id, vio_level, vio_time) values('10023','一级', '2017-08-07 14:28:53');
insert into illegal_2 (user_id, vio_level, vio_time) values('15674','一级', '2015-04-13 12:25:52');
insert into illegal_2 (user_id, vio_level, vio_time) values('87945','三级', '2006-05-21 16:22:16');
insert into illegal_2 (user_id, vio_level, vio_time) values('45345','三级', '2010-05-21 16:22:16');
insert into illegal_2 (user_id, vio_level, vio_time) values('12355','二级', '2020-05-21 16:22:16');
insert into illegal_2 (user_id, vio_level, vio_time) values('12355','一级', '2020-05-20 16:22:16');
insert into illegal_2 (user_id, vio_level, vio_time) values('12355','一级', '2020-04-20 16:22:16');

3.查询数据：

SELECT t3.user_id as user_id, SUBSTR(t3.vio_time,1,4) as vio_year, t3.vio_level as vio_level, count(t3.user_id) as vio_num 
FROM(
	SELECT t1.user_id as user_id, t1.cclzrq as cclzrq, t2.vio_level as vio_level, t2.vio_time as vio_time
	FROM illegal_1 t1
	join illegal_2 t2
	ON t1.user_id = t2.user_id
	WHERE TIMESTAMPDIFF(year, t1.cclzrq, t2.vio_time) <= 3)t3
GROUP BY t3.user_id, SUBSTR(t3.vio_time,1,4), t3.vio_level
ORDER BY t3.user_id, SUBSTR(t3.vio_time,1,4), t3.vio_level