sql语句错误解决方法之:Every derived table must have its own alias

今天在刷力扣的sql题的时候发生了这个错误,Every derived table must have its own alias,翻译过来是每一个派生出来的表都必须有一个自己的别名。

需求：编写一个 SQL 查询，查找所有至少连续出现三次的数字。

建表语句:

Create table If Not Exists 4_Logs (Id int, Num int);
Truncate table 4_Logs;
insert into 4_Logs (Id, Num) values (1, 1);
insert into 4_Logs (Id, Num) values (2, 1);
insert into 4_Logs (Id, Num) values (3, 1);
insert into 4_Logs (Id, Num) values (4, 2);
insert into 4_Logs (Id, Num) values (5, 1);
insert into 4_Logs (Id, Num) values (6, 2);
insert into 4_Logs (Id, Num) values (7, 2);

sql如果写成这样就会报错:Every derived table must have its own alias.

select distinct Num ConsecutiveNums
from (select num,
             lead(num, 1, null) over (order by Id) n2,
             lead(num, 2, null) over (order by Id) n3
      from `4_logs`)
where Num=n2 and num =n3;
select *
from `4_logs`;

原因是mysql规定子查询这个表必须要有别名,至于别名是什么你可以随便写比如t1,加在子查询的后面再执行就不会报错了。

最终sql:

select distinct Num ConsecutiveNums
from 
	(select
			Num,
			lead(Num,1,null) over(order by id) n2,
			lead(Num,2,null) over(order by id) n3
	from 4_Logs
	)t1
where Num = n2 and Num = n3;