Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

31 连续出现的数字 #37

Open
astak16 opened this issue Jan 25, 2022 · 0 comments
Open

31 连续出现的数字 #37

astak16 opened this issue Jan 25, 2022 · 0 comments
Labels

Comments

@astak16
Copy link
Owner

astak16 commented Jan 25, 2022

题目

找出所有至少连续出现三次的数字

create table logs(id int, num int)
insert into logs values
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2);

SQL

select distinct l.num from (
	select num,
	if(@n = num, @c:=@c + 1, @c:=1) as c,
	@n:=num from logs, (select @n:=0, @c:=0) as init
) as l where l.c >= 3;

解析

利用计数来实现,如果这个数和上个数相同,就 +1 ,否则重新赋值为 1

SQL

select distinct a.num from logs a
left join logs b on a.id = b.id + 1
left join logs c on a.id = c.id + 2
where a.num = b.num and a.num = c.num;

解析

logs 表自身连接三次,因为要连续重复出现 3 个数字,所以连接条件是 a.id = b.id + 1a.id = c.id + 2 然后在筛选出数字一样的值,对最后结果进行 distinct 就行了。

@astak16 astak16 added the 中等 label Jan 28, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

1 participant