5

SQL Case条件判断SQL - 我爱睡莲

 1 year ago
source link: https://www.cnblogs.com/houzhiheng/p/17102722.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

SQL Case条件判断SQL

问题描述：在表中取到一些值做出判断，配合监控监测一些表中的数据。使用select case when if 来做条件查询判断

CASE 表达式遍历条件并在满足第一个条件时返回一个值（类似于 if-then-else 语句）。 因此，一旦条件为真，它将停止读取并返回结果。 如果没有条件为真，则返回 ELSE 子句中的值。

如果没有 ELSE 部分并且没有条件为真，则返回 NULL。

case when语法结构

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
MariaDB [test]> desc backup_job_details;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| server_id      | int(11)      | YES  |     | NULL    |       |
| host_name      | varchar(20)  | YES  |     | NULL    |       |
| host_ip        | varchar(50)  | YES  |     | NULL    |       |
| backup_type    | char(20)     | YES  |     | NULL    |       |
| start_time     | datetime     | YES  |     | NULL    |       |
| end_time       | datetime     | YES  |     | NULL    |       |
| backup_process | char(20)     | YES  |     | NULL    |       |
| backup_size    | char(20)     | YES  |     | NULL    |       |
| backup_dir     | varchar(200) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
9 rows in set (0.002 sec)
MariaDB [test]> select * from backup_job_details;
+-----------+-----------------+---------------+-------------+---------------------+---------------------+----------------+-------------+-----------------------------------------------------------------------+
| server_id | host_name       | host_ip       | backup_type | start_time          | end_time            | backup_process | backup_size | backup_dir                                                            |
+-----------+-----------------+---------------+-------------+---------------------+---------------------+----------------+-------------+-----------------------------------------------------------------------+
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-01-29 12:10:01 | 2023-01-29 12:10:04 | success        | 21M         | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-01-29_12-10.zip  |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | full        | 2023-01-30 00:20:01 | 2023-01-30 00:20:03 | success        | 41M         | /localbackup/backup_mariadb/Full_HKTESTMYSQLDB03-2023-01-30_00-20.zip |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-01-30 12:10:01 | 2023-01-30 12:10:04 | success        | 21M         | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-01-30_12-10.zip  |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | full        | 2023-01-31 00:20:01 | 2023-01-31 00:20:05 | success        | 41M         | /localbackup/backup_mariadb/Full_HKTESTMYSQLDB03-2023-01-31_00-20.zip |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-01-31 12:10:01 | 2023-01-31 12:10:04 | success        | 21M         | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-01-31_12-10.zip  |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | full        | 2023-02-01 00:20:01 | 2023-02-01 00:20:03 | success        | 41M         | /localbackup/backup_mariadb/Full_HKTESTMYSQLDB03-2023-02-01_00-20.zip |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | success        | 21M         | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-02-01_12-10.zip  |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | failed         | 0M          |                                                                       |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | failed         | 0M          |                                                                       |
|        54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc         | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | success        | 10M

1.备份表，通过判断备份表中最后一次备份是否成功，或者失败。成功返回success，失败返回failed

select case when backup_process = 'success' then 'success' when backup_process != 'success' then 'failed' end as backup_process from test.backup_job_details order by server_id DESC limit 1;

如果最后一条数据的backup_process 字段数据为success，sql返回结果就是'success'
MariaDB [(none)]> select case when backup_process = 'success' then 'success' when backup_process != 'success' then 'failed' end as backup_process from test.backup_job_details order by server_id DESC limit 1;
+----------------+
| backup_process |
+----------------+
| success        |
+----------------+
1 row in set (0.001 sec)

如果最后一条数据的backup_process 字段数据为其它，sql返回结果就是'failed'
MariaDB [(none)]> select case when backup_process = 'success' then 'success' when backup_process != 'success' then 'failed' end as backup_process from test.backup_job_details order by server_id DESC limit 1;
+----------------+
| backup_process |
+----------------+
| failed        |
+----------------+
1 row in set (0.001 sec)

2.备份表，查询最后一条备份记录后的一段时间段内，有没有新的备份记录产生，如果没有，返回failed；如果备份记录正常产生，返回success。如果新的备份记录没有在规定时间内被记录，此时备份脚本状态可能有异常。

SELECT (CASE WHEN TIMESTAMPDIFF(HOUR,end_time, now()) > 14 THEN "failed" ELSE "success" END )AS TIME_DIFF FROM test.backup_job_details order by end_time DESC limit 1;
通过判断最后一条记录的备份时间跟now()此时的时间比较，判断下一次的记录有没有在规定的时间产生，以此来判断备份脚本的执行状态
MariaDB [(none)]> SELECT (CASE WHEN TIMESTAMPDIFF(HOUR,end_time, now()) > 14 THEN "failed" ELSE "success" END )AS TIME_DIFF FROM test.backup_job_details order by end_time DESC limit 1;
+-----------+
| TIME_DIFF |
+-----------+
| success   |
+-----------+
1 row in set (0.001 sec)

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK