时间戳

首页 > 解决方案 > 根据 case when-then 在 mysql 中设置一个变量

mysql - 根据 case when-then 在 mysql 中设置一个变量

问题描述

我有一个 Projects 表，其中包含三列：Task_ID、Start_Date和End_Date。只要前一行的End_Date和当前行的Start_Date之间的差异不等于零，但它会为每一行递增，我就会尝试两次递增一个名为count的变量。那么，如何根据 CASE when-then 语句更改 mysql 中变量的值？

SQL查询 -

SET @count := 0;
SELECT
*,
LAG(end_date) OVER(ORDER BY start_date) as prev,
DATEDIFF(LAG(end_date) OVER(ORDER BY start_date), start_date) as diff,
(CASE
    WHEN DATEDIFF(LAG(end_date) OVER(ORDER BY start_date), start_date) is NULL
    THEN @count
    WHEN DATEDIFF(LAG(end_date) OVER(ORDER BY start_date), start_date) = 0
    THEN @count
    ELSE @count := @count + 1
END) as cnt
FROM projects
;

输出 -

task_id start_date end_date prev         diff cnt
1      2015-10-01 2015-10-02 NULL        NULL 1
24     2015-10-02 2015-10-03 2015-10-02   0   2
2      2015-10-03 2015-10-04 2015-10-03   0   3
23     2015-10-04 2015-10-05 2015-10-04   0   4
3      2015-10-11 2015-10-12 2015-10-05  -6   5
22     2015-10-12 2015-10-13 2015-10-12   0   6
4      2015-10-15 2015-10-16 2015-10-13  -2   7
21     2015-10-17 2015-10-18 2015-10-16  -1   8
5      2015-10-19 2015-10-20 2015-10-18  -1   9

预期输出 -

task_id start_date end_date prev         diff cnt
1      2015-10-01 2015-10-02 NULL        NULL 0
24     2015-10-02 2015-10-03 2015-10-02   0   0
2      2015-10-03 2015-10-04 2015-10-03   0   0
23     2015-10-04 2015-10-05 2015-10-04   0   0
3      2015-10-11 2015-10-12 2015-10-05  -6   1
22     2015-10-12 2015-10-13 2015-10-12   0   1
4      2015-10-15 2015-10-16 2015-10-13  -2   2
21     2015-10-17 2015-10-18 2015-10-16  -1   3
5      2015-10-19 2015-10-20 2015-10-18  -1   4

标签： mysqlsqlcasecase-when

解决方案

你没有。使用使用窗口函数：

SELECT p.*,
       SUM(diff < 0) OVER (ORDER BY start_date)
FROM (SELECT p.*,
             LAG(end_date) OVER(ORDER BY start_date) as prev,
             DATEDIFF(LAG(end_date) OVER (ORDER BY start_date), start_date) as diff
      FROM projects p
     ) p;

是的SUM(diff < 0)简写SUM(CASE WHEN diff < 0 THEN 1 ELSE 0 END)。MySQL 在算术上下文中将布尔值视为整数。

推荐阅读