SQL实现根据时间戳和增量标记IDU获取最新记录和脱IDU标记

需求说明：表中有 id, info, cnt 三个字段，对应的增量表多idu增量标记字段和时间戳字段ctimestamp。增量表中的 id 会有重复，其他字段 info、cnt 会不断更新，idu为增量标记字段，ctimestamp为IDU操作的时间戳。目的时要做到：
1）获取增量表中的时间戳字段（ctimestamp）为最新值对应的记录、进行id去重；
2）将第一步的查询结果进行脱IDU标记和时间戳字段、合并到最终的表中（不带IDU标记和时间戳字段）。

SQL查询根据时间戳字段和id字段获取最新值的记录

-- 表字段说明：id 会有重复，其他字段 info、cnt 会不断更新，idu为增量标记字段，ctimestamp为IDU操作的时间戳，需求是要获取时间戳字段（ctimestamp）为最新值对应的记录：

-- 脱IDU和时间戳以后的最终目的表（不带增量标记和时间戳字段）
drop table if exists test81;

-- 带IDU标记idu字段和时间戳字段ctimestamp，id字段可能存在重复的值的记录
drop table if exists test81_idu;

-- 基于 test81_idu 生成的 D 的记录，id唯一（去重后无重复记录）
drop table if exists test81_tmp2_d;

-- 基于 test81_idu 生成的 I、U 的记录，id唯一（去重后无重复记录）
drop table if exists test81_tmp3_iu;

-- 创建最终目的表、并构部分已有数据
create table test81 (
  id int not null, 
  info varchar(100), 
  cnt int,
  primary key(id));

 insert into test81 (id,info,cnt) values (1, 'aaa', 31);
 insert into test81 (id,info,cnt) values (2, 'bbb', 33);
 insert into test81 (id,info,cnt) values (3, 'ccc', 35);

注意：如果是GBase8a primary key(id)  主键约束无效。 

select * from test81;

查询结果：

+----+------+------+
| id | info | cnt  |
+----+------+------+
|  1 | aaa  |   31 |
|  2 | bbb  |   33 |
|  3 | ccc  |   35 |
+----+------+------+

-- 创建带IDU标记的表，id存在重复多条记录
create table test81_idu (
  id int not null, 
  info varchar(100), 
  cnt int,
  idu varchar(10),
  ctimestamp timestamp);
  
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (1, 'aaa', 31, 'I', '2023-10-27 12:31:31.123456789');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (2, 'bbb', 33, 'I', '2023-10-27 12:33:33.123456789');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (3, 'ccc', 35, 'I', '2023-10-27 12:35:35.123456789');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (1, 'aaa', 50, 'U', '2023-10-27 12:50:50.123456789');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (1, 'aaa', 41, 'U', '2023-10-27 12:41:41.123456789');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (2, NULL, NULL, 'D', '2023-10-27 12:52:52.123456789');
 
 注意：MySQL支持上面9位（精确到纳秒的输入），只是数据库行为实际上丢弃后面3位，只取前面6位，但不会报错，能执行成功。但如果是GBase8a，则不行，只能指定6位：
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (1, 'aaa', 31, 'I', '2023-10-27 12:31:31.123456');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (2, 'bbb', 33, 'I', '2023-10-27 12:33:33.123456');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (3, 'ccc', 35, 'I', '2023-10-27 12:35:35.123456');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (1, 'aaa', 50, 'U', '2023-10-27 12:50:50.123456');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (1, 'aaa', 41, 'U', '2023-10-27 12:41:41.123456');
 insert into test81_idu (id,info,cnt,idu,ctimestamp) values (2, NULL, NULL, 'D', '2023-10-27 12:52:52.123456');

select * from test81_idu;

执行结果：
+----+------+------+------+---------------------+
| id | info | cnt  | idu  | ctimestamp          |
+----+------+------+------+---------------------+
|  1 | aaa  |   31 | I    | 2023-10-27 12:31:31 |
|  2 | bbb  |   33 | I    | 2023-10-27 12:33:33 |
|  3 | ccc  |   35 | I    | 2023-10-27 12:35:35 |
|  1 | aaa  |   50 | U    | 2023-10-27 12:50:50 |
|  1 | aaa  |   41 | U    | 2023-10-27 12:41:41 |
|  2 | NULL | NULL | D    | 2023-10-27 12:52:52 |
+----+------+------+------+---------------------+

表字段说明：id 会有重复，其他字段 info、cnt 会不断更新，idu为增量标记字段，ctimestamp为IDU操作的时间戳，需求是要获取时间戳字段（ctimestamp）为最新值对应的记录：

脱IDU标记合并处理（中间用多个到临时表）

-- 先查询一下根据时间戳字段和id进行处理，对id去重（同一个id的多条重复记录，只取时间戳最新的一条记录）
SELECT a.id,a.info,a.cnt,a.idu FROM test81_idu a,(
SELECT 
id, MAX(ctimestamp)AS time
FROM test81_idu
GROUP BY id
ORDER BY COUNT(*)DESC)b WHERE a.id=b.id and a.ctimestamp=b.time;

执行结果：
+----+------+------+------+
| id | info | cnt  | idu  |
+----+------+------+------+
|  3 | ccc  |   35 | I    |
|  1 | aaa  |   50 | U    |
|  2 | NULL | NULL | D    |
+----+------+------+------+

-- 如果用 INSERT INTO 则需要先创建目标表、如果用 SELECT INTO 则要保证目标表不存在（执行时会创建目标表）
DROP TABLE IF EXISTS test81_tmp2_d;
-- CREATE TABLE IF NOT EXISTS test81_tmp2_d  LIKE test81_idu;

-- 根据id和时间戳查询最新记录、并只显示idu='D'的结果
-- MySQL不支持的方式：
-- SELECT id,info,cnt INTO test81_tmp2_d FROM test81_idu;

-- MySQL能支持的方式：
DROP TABLE IF EXISTS test81_tmp2_d;
CREATE TABLE test81_tmp2_d 
(
SELECT a.id,a.info,a.cnt,a.idu FROM test81_idu a,(
SELECT 
id, MAX(ctimestamp)AS time
FROM test81_idu
GROUP BY id
ORDER BY COUNT(*)DESC)b WHERE a.id=b.id AND a.ctimestamp=b.time AND a.idu='D'
);

-- 查询结果：
select * from test81_tmp2_d;
+----+------+------+------+
| id | info | cnt  | idu  |
+----+------+------+------+
|  2 | NULL | NULL | D    |
+----+------+------+------+

-- 根据id和时间戳查询最新记录、并只显示idu='IU'的结果
-- MySQL能支持的方式
DROP TABLE IF EXISTS test81_tmp3_iu;
CREATE TABLE test81_tmp3_iu 
(
SELECT a.id,a.info,a.cnt,a.idu FROM test81_idu a,(
SELECT 
id, MAX(ctimestamp)AS time
FROM test81_idu
GROUP BY id
ORDER BY COUNT(*)DESC)b WHERE a.id=b.id AND a.ctimestamp=b.time AND (a.idu IN ('I','U'))
);

-- 查询结果：
select * from test81_tmp3_iu;
+----+------+------+------+
| id | info | cnt  | idu  |
+----+------+------+------+
|  3 | ccc  |   35 | I    |
|  1 | aaa  |   50 | U    |
+----+------+------+------+

-- 将'D'的数据从最终目标表删除
DELETE FROM test81 WHERE id in (SELECT DISTINCT id FROM test81_tmp2_d);

-- 'D' 记录删除后，查看目的表数据
select * from test81;
+----+------+------+
| id | info | cnt  |
+----+------+------+
|  1 | aaa  |   31 |
|  3 | ccc  |   35 |
+----+------+------+

-- 将'I'和'U'的数据插入或更新到最终的目标表
-- merge into, 目的表的关联id存在则update、不存在则insert：

-- GBase8a 使用 merge into （MySQL 不支持该语法）
-- merge into test81 t1 using test81_tmp3_iu tmp on t1.id=tmp.id when matched then update set t1.info=tmp.info,t1.cnt=tmp.cnt when not matched then insert(id,info,cnt)values(tmp.id,tmp.info,tmp.cnt);

-- MySQL 使用 insert into （GBase8a 不支持该语法）
INSERT INTO test81(id,info,cnt) SELECT id,info,cnt FROM test81_tmp3_iu ON DUPLICATE KEY UPDATE info=VALUES(info), cnt=VALUES(cnt);

-- 'I'和'U'合并到目的表后，查询结果
select * from test81;
+----+------+------+
| id | info | cnt  |
+----+------+------+
|  1 | aaa  |   50 |
|  3 | ccc  |   35 |
+----+------+------+

到此为止，整个脱IDU表级和时间戳的流程处理完毕。