MySQL连续出现的数字
SQL架构
Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1');
insert into Logs (Id, Num) values ('2', '1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4', '2');
insert into Logs (Id, Num) values ('5', '1');
insert into Logs (Id, Num) values ('6', '2');
insert into Logs (Id, Num) values ('7', '2');
题目描述
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。
题解
方法：用 DISTINCT 和 WHERE 语句
算法
连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num;
+------+------+------+------+------+------+
| Id | Num | Id | Num | Id | Num |
+------+------+------+------+------+------+
| 1 | 1 | 2 | 1 | 3 | 1 |
+------+------+------+------+------+------+
1 row in set (0.01 sec)
注意：前两列来自 l1 ，接下来两列来自 l2 ，最后两列来自 l3 。
然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num;
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
答1
解题思路
连结
将logs起3个别名，然后t1.id=t2.id+1 t2.id=t3.id+1，3个表的num也要一样
SELECT
DISTINCT t2.num AS ConsecutiveNums
FROM
Logs t1,
Logs t2,
Logs t3
WHERE
t1.id = t2.id + 1
AND t2.id = t3.id + 1
AND t1.num = t2.num
AND t2.num = t3.num;
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
答2
SELECT DISTINCT a.Num "ConsecutiveNums"
FROM Logs a
INNER JOIN Logs b ON a.id + 1 = b.id
INNER JOIN Logs c ON b.id + 1 = c.id
WHERE a.Num = b.Num AND b.Num = c.Num;
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
答3
①首先遍历一遍整张表，找出每个数字的连续重复次数
具体方法为：
初始化两个变量，一个为pre，记录上一个数字；一个为count，记录上一个数字已经连续出现的次数。
然后调用 if() 函数，如果 pre 和当前行数字相同，count 加 1 极为连续出现的次数；如果不同，意味着重新开始一个数字，count重新从1开始。
最后，将当前的Num数字赋值给pre，开始下一行扫描。
select
Num, #当前的Num 数字
if(@pre=Num,@count := @count+1,@count := 1) as nums, #判断 和 计数
@pre:=Num as mer #将当前Num赋值给pre
from Logs;
+------+------+------+
| Num | nums | mer |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 2 | 1 | 2 |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 2 | 2 | 2 |
+------+------+------+
7 rows in set (0.00 sec)
上面这段代码执行结果就是一张三列为Num,count as nums,pre的表。
②将上面表的结果中，重复次数大于等于3的数字选出，再去重即为连续至少出现三次的数字。
select
distinct Num as ConsecutiveNums
from
(select Num,
if(@pre=Num,@count := @count+1,@count := 1) as nums,
@pre:=Num
from Logs as l ,
(select @pre:= null,@count:=1) as pc
) as n
where nums >=3;
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
注意：pre初始值最好不要赋值为一个数字，因为不确定赋值的数字是否会出现在测试表中。
答4
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
SELECT Num,
CASE WHEN Num = @pre_num THEN @count:=@count+1
ELSE @count:=1 END AS continue_count,
@pre_num:=Num AS pre_num
FROM `Logs`, (SELECT @pre_num:=NULL, @count:=1) AS init
) AS temp
WHERE continue_count = 3;
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
知识点
MySql 中关键字 case when then else end 的用法
SELECT
case -------------如果
when sex='1' then '男' -------------sex='1'，则返回值'男'
when sex='2' then '女' -------------sex='2'，则返回值'女'
else '其他' -------------其他的返回'其他’
end -------------结束
from sys_user
整体理解： 在sys_user表中如果sex=‘1’，则返回值’男’如果sex=‘2’，则返回值’女’ 否则返回’其他’
case具有两种格式，简单case函数和case搜索函数
--简单case函数
case sex
when '1' then '男'
when '2' then '女’
else '其他' end
--case搜索函数
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式。
还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。
--比如说，下面这段sql，你永远无法得到“第二类”这个结果
case when col_1 in ('a','b') then '第一类'
when col_1 in ('a') then '第二类'
else '其他' end
SQL架构
CREATE TABLE `n_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`sex` decimal(10,0) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('1', '张一', '0');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('2', '张二', '1');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('3', '张三', '0');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('4', '张四', '0');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('5', '张五', '2');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('6', '张六', '1');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('7', '张七', '2');
INSERT INTO `mysqldemo`.`n_users` (`id`, `name`, `sex`) VALUES ('8', '张八', '1');
1、表中的"sex"是用数字表示的，希望用中文显示。可以使用case语句：
select u.id,u.name,u.sex,
(case u.sex
when 1 then '男'
when 2 then '女'
else '其他'
end
)性别
from n_users u;
+----+--------+-----+--------+
| id | name | sex | 性别 |
+----+--------+-----+--------+
| 1 | 张一 | 0 | 其他 |
| 2 | 张二 | 1 | 男 |
| 3 | 张三 | 0 | 其他 |
| 4 | 张四 | 0 | 其他 |
| 5 | 张五 | 2 | 女 |
| 6 | 张六 | 1 | 男 |
| 7 | 张七 | 2 | 女 |
| 8 | 张八 | 1 | 男 |
+----+--------+-----+--------+
8 rows in set (0.00 sec)
2、如果不希望列表中出现"sex"列，语句如下：
select u.id,u.name,
(case u.sex
when 1 then '男'
when 2 then '女'
else '其他'
end
)性别
from n_users u;
+----+--------+--------+
| id | name | 性别 |
+----+--------+--------+
| 1 | 张一 | 其他 |
| 2 | 张二 | 男 |
| 3 | 张三 | 其他 |
| 4 | 张四 | 其他 |
| 5 | 张五 | 女 |
| 6 | 张六 | 男 |
| 7 | 张七 | 女 |
| 8 | 张八 | 男 |
+----+--------+--------+
8 rows in set (0.00 sec)
3、将sum与case结合使用，可以实现分段统计。
如果现在希望将上表中各种性别的人数进行统计，sql语句如下：
select
sum(case u.sex when 1 then 1 else 0 end)男性,
sum(case u.sex when 2 then 1 else 0 end)女性,
sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空
from n_users as u;
+--------+--------+--------------+
| 男性 | 女性 | 性别为空 |
+--------+--------+--------------+
| 3 | 2 | 3 |
+--------+--------+--------------+
1 row in set (0.00 sec)
select
count(case when u.sex=1 then 1 end)男性,
count(case when u.sex=2 then 1 end)女,
count(case when u.sex <>1 and u.sex<>2 then 1 end)性别为空
from n_users u;
+--------+-----+--------------+
| 男性 | 女 | 性别为空 |
+--------+-----+--------------+
| 3 | 2 | 3 |
+--------+-----+--------------+
1 row in set (0.00 sec)
MySQL-IF()函数
定义
IF函数根据条件的结果为true或false，返回第一个值，或第二个值
语法
IF(condition, value_if_true, value_if_false)
参数
| 参数 | 描述 |
|---|---|
| condition | 必须，判断条件 |
| value_if_true | 可选，当条件为true值返回的值 |
| condition | 可选，当条件为false值返回的值 |
例子
SELECT IF(500<1000, 5, 10);
SELECT IF(STRCMP("hello","bye") = 0, "YES", "NO");
mysql> select *,if(sex=1,"男","女") as '性别' from n_users;
+----+--------+-----+--------+
| id | name | sex | 性别 |
+----+--------+-----+--------+
| 1 | 张一 | 0 | 女 |
| 2 | 张二 | 1 | 男 |
| 3 | 张三 | 0 | 女 |
| 4 | 张四 | 0 | 女 |
| 5 | 张五 | 2 | 女 |
| 6 | 张六 | 1 | 男 |
| 7 | 张七 | 2 | 女 |
| 8 | 张八 | 1 | 男 |
+----+--------+-----+--------+
8 rows in set (0.00 sec)
MySQL IF CASE 例子
SELECT USER_CODE AS '用户编码',
IF(USER_STATE = 1, '正常', '冻结') AS '用户状态',
(
CASE USER_TYPE
WHEN 1 THEN
'用户'
WHEN 2 THEN
'商家'
WHEN 3 THEN
'自营'
ELSE
'未知'
END
) AS '用户类型'
FROM
USER_INFO LIMIT 10;