MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE orginal_table (MID CHAR(1), STATE TINYINT, CALL_TIME DATETIME, RECORD_RANK TINYINT);
INSERT INTO orginal_table VALUES
('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);

CREATE TABLE insert_table (MID CHAR(1), STATE TINYINT, CALL_TIME DATETIME);
INSERT INTO insert_table VALUES
(   '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');

Records: 7  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0


-- let's look where inserted rows must be placed
SELECT * FROM orginal_table
UNION ALL
SELECT *, NULL FROM insert_table
ORDER BY 1,3


MID
STATE
CALL_TIME
RECORD_RANK


a
1
2020-12-18 09:00:00
1

a
2
2020-12-19 09:00:00
2

a
2
2020-12-30 09:00:00
null

b
1
2020-12-18 09:00:02
1

b
2
2020-12-19 09:00:02
null

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

c
1
2020-12-21 09:00:03
null

d
1
2020-12-19 09:00:00
1

e
1
2020-12-30 09:00:00
null

f
1
2020-12-30 09:00:00
null

f
2
2020-12-31 09:00:00
null


	insert into orginal_table (mid, state, call_time, record_rank)
	select mid, state, call_time,
		1 + (
			select count(*)
			from orginal_table o
			where o.mid = i.mid
		) + (
			select count(*) 
			from insert_table i1 
			where i1.mid = i.mid and i1.call_time < i.call_time
		) as record_rank
	from insert_table i

Records: 6  Duplicates: 0  Warnings: 0


SELECT * FROM orginal_table ORDER BY 1,3;


MID
STATE
CALL_TIME
RECORD_RANK


a
1
2020-12-18 09:00:00
1

a
2
2020-12-19 09:00:00
2

a
2
2020-12-30 09:00:00
3

b
1
2020-12-18 09:00:02
1

b
2
2020-12-19 09:00:02
2

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

c
1
2020-12-21 09:00:03
4

d
1
2020-12-19 09:00:00
1

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

MID	STATE	CALL_TIME	RECORD_RANK
a	1	2020-12-18 09:00:00	1
a	2	2020-12-19 09:00:00	2
a	2	2020-12-30 09:00:00	null
b	1	2020-12-18 09:00:02	1
b	2	2020-12-19 09:00:02	null
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
c	1	2020-12-21 09:00:03	null
d	1	2020-12-19 09:00:00	1
e	1	2020-12-30 09:00:00	null
f	1	2020-12-30 09:00:00	null
f	2	2020-12-31 09:00:00	null