根据语句引用多个其他变量时的情况，SQL在新变量中返回1,0 [英] SQL Return 1,0 in new variable based on case when statement referring to multiple other variables

问题描述

我正在尝试创建一个新变量，该变量在MySQL中基于一系列日期和阶梯级别(null-E)填充为1(真)，0(假).

I'm trying to create a new variable that populates with a 1 (true), 0 (false) in MySQL based on a series of dates and ladder levels (null-E).

请参阅小提琴: http://sqlfiddle.com/#!9/9975e1

record_dates和ladder_levels不一定按顺序排列.我想通过一个当(?)语句返回类似情况的返回ladder_change字段:

Where the record_dates and ladder_levels aren't necessarily in sequential order. I'd like to return the ladder_change fieldvia a case when (?) statement, that says something like:

• 首先，仅在匹配的ID中查找(即仅查找ID 324)
  • 然后，类似于:case when record_date2 > record_date1 AND (ladder_level2 < ladder_level1 OR ladder_level2>ladder_level1) then 1, else 0
  • First, look only within matching IDs (i.e. just for ID 324)
    • Then, something like: case when record_date2 > record_date1 AND (ladder_level2 < ladder_level1 OR ladder_level2>ladder_level1) then 1, else 0

    有关如何实现此目标的任何提示?

    Any tips on how to achieve this?

    推荐答案

    • 由于未定义主键，因此我假设在creation_date和ladder_level上按顺序输入数据. 记住，数据以无序方式存储在MySQL中.
    • 首先，我们必须使用子选择查询以所需顺序获取数据(如上一步中突出显示).值得注意的是，Order By是在Select子句之后执行的；因此我们需要首先对数据进行排序，然后将结果集用作派生表.
    • 现在，我们将借助用户定义的帮助变量(会话级的持久性和可访问性).在另一个派生表user_init_vars中，我们对其进行了初始化.
    • 在Select子句中，我们将当前行的值与上一行的值进行比较.比较之后，我们将变量值设置为当前行的值.您可以将其视为循环技术，我们在其他编程语言(例如PHP，C ++，Java等)中使用了这种技术.
    • Case .. When 表达式用于比较，并确定ladder_change值.
    • Since there is no Primary Key defined, I am assuming that data is entered in sequential order on creation_date and ladder_level. Remember that data is stored in unordered fashion in MySQL.
    • Firstly, we have to use a sub-select query to get the data in required order (as highlighted in previous step). It is noteworthy that Order By is executed after Select clause; so we will need to first get the data sorted, and then use the result-set as a Derived Table.
    • Now, we will take the help of User-defined variables (session-level persistent and accessible). In another derived table user_init_vars, we initialize them.
    • In the Select clause, we compare the current row's value against the previous row value. After comparison, we set the variable value to current row's value. You can think of it as looping technique, which we use in other programming languages like PHP, C++, Java etc.
    • Case .. When expressions are used for comparison, and determining the ladder_change value.

    查询#1

    SELECT 
      dt.ID, 
      CASE WHEN DATE(@rd) <> DATE(dt.record_date) AND 
                dt.ladder_level > @ll 
           THEN 1 
           ELSE 0 
      END AS ladder_change, 
      @rd := dt.record_date AS record_date, 
      @ll := dt.ladder_level AS ladder_level 
    FROM (SELECT ID, record_date, ladder_level 
          FROM conv_example 
          ORDER BY ID, record_date, ladder_level) AS dt 
    CROSS JOIN (SELECT @rd := '', 
                       @ll := '') AS user_init_vars;
    
    | ID    | ladder_change | record_date         | ladder_level |
    | ----- | ------------- | ------------------- | ------------ |
    | 324   | 0             | 2016-09-15 00:00:00 | a            |
    | 324   | 0             | 2016-09-15 00:00:00 | b            |
    | 324   | 0             | 2017-04-07 00:00:00 | b            |
    | 324   | 0             | 2017-04-07 00:00:00 | c1           |
    | 324   | 0             | 2018-09-08 00:00:00 | c1           |
    | 324   | 0             | 2018-09-08 00:00:00 | e            |
    | 1234  | 0             | 2013-04-03 00:00:00 |              |
    | 1234  | 0             | 2014-07-03 00:00:00 | a            |
    | 1234  | 1             | 2015-04-01 00:00:00 | b            |
    | 1234  | 1             | 2016-09-15 00:00:00 | d            |
    | 1234  | 0             | 2017-02-04 00:00:00 | b            |
    | 1234  | 0             | 2017-04-03 00:00:00 | b            |
    | 1234  | 1             | 2017-04-07 00:00:00 | c1           |
    | 1234  | 1             | 2018-09-08 00:00:00 | e            |
    | 31431 | 0             | 2013-04-03 00:00:00 |              |
    | 31431 | 0             | 2014-07-03 00:00:00 | a            |
    | 31431 | 1             | 2017-04-07 00:00:00 | c1           |
    | 31431 | 1             | 2018-09-08 00:00:00 | e            |
    

    ---

    在DB Fiddle上查看

    这篇关于根据语句引用多个其他变量时的情况，SQL在新变量中返回1,0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！