从表中检查历史记录并根据记录在MySQL中标记记录 [英] Checking History From Table And Marking The Record According To That In Mysql

问题描述

我有一个具有以下结构的源表

I have a source table with following structure

表名称:示例

    +---+-------+----+--------------------+---------+
    | id|   name| e_id|    date           | a_code |
    +---+-------+----+--------------------+---------+
    |  1|M Malik|A10A|2002-12-03 00:00:00|   018 |
    |  3|M Malik|A10A|1999-02-03 00:00:00|   018 |
    |  4| S Raza|A12A|2006-10-13 00:00:00|   018 |
    |  5| S Raza|A12A|2010-01-23 00:00:00|   018 |
    +---+-------+----+--------------------+---------+

现在，我必须检查样本中的每条记录，如果a_code是否在两年或更早之前应用于同一位员工，如果适用，则将此记录标记为1级，否则将此记录标记为2级 所以我的结果表看起来像这样

Now i have to check every record in the sample that if a_code is apply on same employee two or more year ago if apply than mark this as level 1 else mark this record as level 2 So my result table would look like this

表名:结果

    +---+-------+----+--------------------+---------+
    | id|   name| e_id|    date          |a_code|level
    +---+-------+----+--------------------+---------+
    |  1|M Malik|A10A|2002-12-03 00:00:00|   018 | 1
    |  3|M Malik|A10A|1999-02-03 00:00:00|   018 | 2
    |  4| S Raza|A12A|2006-10-13 00:00:00|   018 | 2
    |  5| S Raza|A12A|2010-01-23 00:00:00|   018 | 1
    +---+-------+----+--------------------+---------+

我尝试通过自连接解决问题，但我进行笛卡尔运算并复制记录，但给出的结果不准确. 我该如何实现...?

I try to solve it with self joins but i make Cartesian and duplicate the record and give not accurate results. How can i achieve that...?

推荐答案

例如:

SELECT x.*
     , CASE WHEN y.a_code = x.a_code THEN 1 ELSE 2 END level 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.e_id = x.e_id 
   AND y.a_code = x.a_code 
   AND y.date < x.date - INTERVAL 2 YEAR;

Name不应是此表中的一列.

Name should not be a column in this table.

这篇关于从表中检查历史记录并根据记录在MySQL中标记记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！