MySQL连续出现的数字

小灰灰 2023-10-06 18:48 45阅读 0赞

#### MySQL连续出现的数字 ####

*  SQL架构
 *  题目描述
 *  题解
 *   *  答1
 *  答2
 *  答3
 *   *  ①首先遍历一遍整张表，找出每个数字的连续重复次数
     *  ②将上面表的结果中，重复次数大于等于3的数字选出，再去重即为连续至少出现三次的数字。
 *  答4
 *  知识点
 *   *  MySql 中关键字 case when then else end 的用法
     *  case具有两种格式，简单case函数和case搜索函数
     *   *  SQL架构
         *  1、表中的"sex"是用数字表示的，希望用中文显示。可以使用case语句：
         *  2、如果不希望列表中出现"sex"列，语句如下：
         *  3、将sum与case结合使用，可以实现分段统计。
     *  MySQL-IF()函数
     *  MySQL IF CASE 例子

## 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)

参数

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>condition</td> 
   <td>必须，判断条件</td> 
  </tr> 
  <tr> 
   <td>value_if_true</td> 
   <td>可选，当条件为true值返回的值</td> 
  </tr> 
  <tr> 
   <td>condition</td> 
   <td>可选，当条件为false值返回的值</td> 
  </tr> 
 </tbody> 
</table>

例子

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;