mysql - 插入数据并用排名填充列

Question

原始数据:原始表

MID STATE CALL_TIME         RECORD_RANK
a   1    2020-12-18 09:00:00        1
a   2    2020-12-19 09:00:00        2
b   1    2020-12-18 09:00:02        1
c   1    2020-12-18 09:00:03        1
c   1    2020-12-19 09:00:03        2
c   1    2020-12-20 09:00:03        3
d   1    2020-12-19 09:00:00        1

我想插入的数据:insert_table

   MID  STATE      CALL_TIME  
   a    2     2020-12-30 09:00:00      
   b    2     2020-12-19 09:00:02   
   c    1     2020-12-21 09:00:03 
   e    1     2020-12-30 09:00:00 
   f    1     2020-12-30 09:00:00 
   f    2     2020-12-31 09:00:00

目标

原始数据将从第二个数据插入。

对于原始数据和插入数据，对 MID and CALL_TIME是独特的。

没有RECORD_RANK插入数据中的列，但 RECORD_RANK将根据 MID and CALL_TIME columns 计算插入时。当用不同的 CALL_TIME 复制 MID 时，带有 MID 的 RECORD_RANK 的值将加 1。初始值为 1。

insert_table 中最早的行总是晚于 orginal_table 中具有相同 MID 的最新行。

预期的示例结果如下:

MID  STATE      CALL_TIME         RECORD_RANK
a    1    2020-12-18 09:00:00        1
a    2    2020-12-19 09:00:00        2
b    1    2020-12-18 09:00:02        1
c    1    2020-12-18 09:00:03        1
c    1    2020-12-19 09:00:03        2
c    1    2020-12-20 09:00:03        3
d    1    2020-12-19 09:00:00        1
a    2    2020-12-30 09:00:00        3
b    2    2020-12-19 09:00:02        2  
c    1    2020-12-21 09:00:03        4
e    1    2020-12-30 09:00:00        1
f    1    2020-12-30 09:00:00        1 
f    2    2020-12-31 09:00:00        2

备注

mysql 版本:5.5.47-log

Answer 1

如果 insert_table 中最简单的行总是晚于 orginal_table 中的最新行同MID那么你可以使用 BEFORE INSERT 触发器:

CREATE TRIGGER tr_bi_original
BEFORE INSERT
ON orginal_table
FOR EACH ROW
SET NEW.RECORD_RANK = (SELECT COALESCE(COUNT(*), 0) + 1
                       FROM orginal_table
                       WHERE NEW.MID = orginal_table.MID)

创建触发器后，您可以简单地添加新行

INSERT INTO orginal_table
SELECT *, NULL FROM insert_table;

RECORD_RANK 的新值将由触发器添加。
fiddle

如果来自两个表的行将混合和 RECORD_RANK对于已经存在于 orginal_table 中的某些行必须更改，则无法使用一个查询执行操作(因为需要插入新行和更新现有行)。在这种情况下，我建议为 RECORD_RANK 插入具有任何 (NULL) 值的行列，然后重新计算表中所有行的列值。