SQL技术问询：用GROUP BY与MAX筛选双源最高版本记录

首先给出建表和测试数据的SQL：

CREATE TABLE table_name(
    p_id VARCHAR(50),
    d_id VARCHAR(50),
    t_id VARCHAR(50),
    version INT,
    source VARCHAR(50),
    name VARCHAR(50),
    county VARCHAR(50)
);
        
INSERT INTO table_name VALUES
('p1','d1','t1',2,'online','penny','usa'),
('p1','d1','t1',2,'manual','penny','india'),
('p1','d1','t1',1,'online','penny','india'),
('p1','d1','t1',1,'manual','penny','usa'),
('p2','d2','t2',4,'online','david','india'),
('p2','d2','t2',4,'online','david','usa'),
('p2','d2','t2',1,'online','david','usa'),
('p2','d2','t2',1,'manual','david','india'),
('P3','d3','d3',3,'online','raj','india');

方案1：筛选最高version下同时存在双来源的记录 #

如果需求是每个(p_id,d_id,t_id)组合的最高version下必须同时有online和manual两种source，用下面的查询，会返回p1-d1-t1的version2记录：

WITH cte_max_version AS (
    -- 先拿到每个组合的最高版本
    SELECT p_id, d_id, t_id, MAX(version) AS max_version
    FROM table_name
    GROUP BY p_id, d_id, t_id
),
cte_valid_versions AS (
    -- 筛选出最高版本下存在两种source的组合
    SELECT tn.p_id, tn.d_id, tn.t_id, tn.version
    FROM table_name tn
    JOIN cte_max_version mv 
        ON tn.p_id = mv.p_id 
        AND tn.d_id = mv.d_id 
        AND tn.t_id = mv.t_id 
        AND tn.version = mv.max_version
    GROUP BY tn.p_id, tn.d_id, tn.t_id, tn.version
    HAVING COUNT(DISTINCT source) = 2
)
-- 返回符合条件的所有记录
SELECT tn.*
FROM table_name tn
JOIN cte_valid_versions vv 
    ON tn.p_id = vv.p_id 
    AND tn.d_id = vv.d_id 
    AND tn.t_id = vv.t_id 
    AND tn.version = vv.version;

方案2：匹配你期望的结果（仅返回p2-d2-t2的version4） #

如果需求是组合整体存在两种source，且最高version下只有单一source，用下面的查询，会精准返回你要的结果：

WITH cte_max_version AS (
    SELECT p_id, d_id, t_id, MAX(version) AS max_version
    FROM table_name
    GROUP BY p_id, d_id, t_id
),
cte_has_both_sources AS (
    -- 先筛选出整体有两种source的组合
    SELECT p_id, d_id, t_id
    FROM table_name
    GROUP BY p_id, d_id, t_id
    HAVING COUNT(DISTINCT source) = 2
),
cte_single_source_version AS (
    -- 再筛选出最高版本下只有单一source的组合
    SELECT tn.p_id, tn.d_id, tn.t_id, tn.version
    FROM table_name tn
    JOIN cte_max_version mv 
        ON tn.p_id = mv.p_id 
        AND tn.d_id = mv.d_id 
        AND tn.t_id = mv.t_id 
        AND tn.version = mv.max_version
    GROUP BY tn.p_id, tn.d_id, tn.t_id, tn.version
    HAVING COUNT(DISTINCT source) = 1
)
-- 关联条件返回最终记录
SELECT tn.*
FROM table_name tn
JOIN cte_single_source_version ssv 
    ON tn.p_id = ssv.p_id 
    AND tn.d_id = ssv.d_id 
    AND tn.t_id = ssv.t_id 
    AND tn.version = ssv.version
JOIN cte_has_both_sources hbs 
    ON tn.p_id = hbs.p_id 
    AND tn.d_id = hbs.d_id 
    AND tn.t_id = hbs.t_id;

原查询的问题说明 #

你原来的查询用COUNT(*)>1判断是否存在两种source是错误的——这个条件只能说明该版本下有多条记录，但不能保证是不同source（比如p2的version4有两条online记录，count是2，但只有一种source）。正确的做法是用COUNT(DISTINCT source)来统计不同的source数量，这样才能准确判断是否包含两种来源。

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