当前位置：首页 > news >正文

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

news 2025/8/20 12:36:52

需求说明：表中有 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表级和时间戳的流程处理完毕。

查看全文

http://www.lryc.cn/news/217598.html