如何在SQLite中按ID分组关联代码集并筛选目标阳性数据？

需求：收集TEST_RESULT为POSITIVE且代码集包含U071或与U071配对的其他代码的数据，需将同一ID对应的代码集分组，每组计为1行。

现有Table 1数据：

当前问题：现有代码仅筛选出CODE为U071的记录，无法关联同一ID下的其他代码，导致ID 1的T34、ID 3的N99被排除，且无法将同一ID的代码集识别为一组。

期望生成的Table 2：

注：ID 1显示T34或U071均可，ID 3同理，只需保证每个ID对应一行记录。

当前错误代码：

CREATE TABLE table2 AS
SELECT ID, TEST_RESULT, DATE, CODE
FROM table1
WHERE TEST_RESULT='POSITIVE' AND CODE = '%U071%'
group by ID 
ORDER BY ID ASC;

要实现需求，核心是先筛选出所有包含U071的ID，再基于这些ID提取对应的数据并按ID分组取一行。以下是两种可行方法：

方法一：子查询筛选目标ID+分组取行 #

先找出所有符合条件的目标ID，再从原表提取这些ID的记录，最后按ID分组（因同一ID的TEST_RESULT和DATE一致，可加入分组避免数据库规则报错）：

CREATE TABLE table2 AS
SELECT ID, TEST_RESULT, DATE, CODE
FROM table1
WHERE TEST_RESULT = 'POSITIVE'
  AND ID IN (
    SELECT DISTINCT ID
    FROM table1
    WHERE TEST_RESULT = 'POSITIVE' AND CODE = 'U071'
  )
GROUP BY ID, TEST_RESULT, DATE
ORDER BY ID ASC;

方法二：窗口函数取每组首行 #

通过窗口函数为每个ID的记录排序，精准控制取哪一行（比如优先取U071）：

CREATE TABLE table2 AS
WITH ranked_data AS (
  SELECT 
    ID, TEST_RESULT, DATE, CODE,
    -- 优先取U071，可根据需求调整排序规则
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN CODE = 'U071' THEN 0 ELSE 1 END) AS rn
  FROM table1
  WHERE TEST_RESULT = 'POSITIVE'
    AND ID IN (
      SELECT DISTINCT ID
      FROM table1
      WHERE TEST_RESULT = 'POSITIVE' AND CODE = 'U071'
    )
)
SELECT ID, TEST_RESULT, DATE, CODE
FROM ranked_data
WHERE rn = 1
ORDER BY ID ASC;

关键说明 #

• 子查询SELECT DISTINCT ID FROM table1 WHERE TEST_RESULT = 'POSITIVE' AND CODE = 'U071'负责锁定所有包含U071的ID，确保不会漏掉同一ID下的其他关联代码。
• 方法二的窗口函数排序规则可灵活调整，比如想按代码字母顺序取首行，可将ORDER BY后的内容换成CODE。

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