如何筛选证券最早激活记录并获取两查询结果的交集ID

一、获取每个id最早的激活记录 #

针对每个id存在多条激活记录的情况，以下两种方法可以筛选出最早的那条：

方法1：GROUP BY 结合 MIN() #

如果只需要id和对应的最早激活时间，用这个最简单：

SELECT id, MIN(timestamp) AS earliest_activation_time
FROM sval
WHERE fid = 4044 AND val = 'True'
GROUP BY id;

如果需要保留该条记录的所有字段（比如fid、val），可以通过子查询关联原表：

SELECT s.*
FROM sval s
INNER JOIN (
    SELECT id, MIN(timestamp) AS min_ts
    FROM sval
    WHERE fid = 4044 AND val = 'True'
    GROUP BY id
) t ON s.id = t.id AND s.timestamp = t.min_ts
WHERE s.fid = 4044 AND s.val = 'True';

方法2：窗口函数 ROW_NUMBER() #

这种方式更灵活，适合需要处理复杂排序逻辑的场景：

SELECT id, fid, timestamp, val
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp ASC) AS rn
    FROM sval
    WHERE fid = 4044 AND val = 'True'
) sub_query
WHERE rn = 1;

说明：PARTITION BY id 按id分组，ORDER BY timestamp ASC 让每组内记录按时间升序排列，rn=1 就代表每组的第一条（即最早的激活记录）。

二、筛选同时存在于两个结果中的id #

要找出既在激活记录里，又在ival表指定条件下的id，三种常用实现方式：

方法1：IN 子查询 #

SELECT DISTINCT id
FROM sval
WHERE fid = 4044 AND val = 'True'
AND id IN (
    SELECT DISTINCT id
    FROM ival
    WHERE fid = 3994 AND val = 0
);

方法2：INNER JOIN #

SELECT DISTINCT s.id
FROM sval s
INNER JOIN ival i ON s.id = i.id
WHERE s.fid = 4044 AND s.val = 'True'
AND i.fid = 3994 AND i.val = 0;

方法3：EXISTS 子查询 #

SELECT DISTINCT id
FROM sval s
WHERE fid = 4044 AND val = 'True'
AND EXISTS (
    SELECT 1
    FROM ival i
    WHERE i.id = s.id
    AND i.fid = 3994 AND i.val = 0
);

内容的提问来源于stack exchange，提问作者ifrj