如何关联t1、t2表，查询t1中V字段子串对应的最小VA值

实现方案 #

逻辑说明 #

首先将t1表的V字段按空格拆分为单个子串，每个子串关联t2表取对应的VA值，再按PL分组筛选出VA最小的记录，最终输出所需字段即可。

不同数据库版本的SQL实现 #

MySQL 8.0+ 版本

WITH RECURSIVE split_v AS (
    -- 递归拆分V字段的单个值
    SELECT 
        PL,
        V,
        SUBSTRING_INDEX(V, ' ', 1) AS v_item,
        SUBSTRING(V, LENGTH(SUBSTRING_INDEX(V, ' ', 1)) + 2) AS remaining
    FROM t1
    UNION ALL
    SELECT 
        PL,
        V,
        SUBSTRING_INDEX(remaining, ' ', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ' ', 1)) + 2)
    FROM split_v
    WHERE remaining <> ''
),
ranked AS (
    -- 按VA排序取最小的那条
    SELECT 
        s.PL,
        s.V,
        t2.VA,
        t2.V AS match_v,
        ROW_NUMBER() OVER(PARTITION BY s.PL ORDER BY t2.VA ASC) AS rn
    FROM split_v s
    INNER JOIN t2 ON s.v_item = t2.V
)
SELECT PL, V, VA, CONCAT(match_v, ' is first and has VA ', VA, ' in t2') AS EXPLANATION
FROM ranked
WHERE rn = 1
ORDER BY PL;

PostgreSQL 版本

WITH split_v AS (
    SELECT 
        PL,
        V,
        unnest(string_to_array(V, ' ')) AS v_item
    FROM t1
),
ranked AS (
    SELECT 
        s.PL,
        s.V,
        t2.VA,
        t2.V AS match_v,
        ROW_NUMBER() OVER(PARTITION BY s.PL ORDER BY t2.VA ASC) AS rn
    FROM split_v s
    INNER JOIN t2 ON s.v_item = t2.V
)
SELECT PL, V, VA, match_v || ' is first and has VA ' || VA || ' in t2' AS EXPLANATION
FROM ranked
WHERE rn = 1
ORDER BY PL;

Spark SQL / Hive 版本

WITH split_v AS (
    SELECT 
        PL,
        V,
        v_item
    FROM t1
    LATERAL VIEW explode(split(V, ' ')) tmp AS v_item
),
ranked AS (
    SELECT 
        s.PL,
        s.V,
        t2.VA,
        t2.V AS match_v,
        ROW_NUMBER() OVER(PARTITION BY s.PL ORDER BY t2.VA ASC) AS rn
    FROM split_v s
    INNER JOIN t2 ON s.v_item = t2.V
)
SELECT PL, V, VA, concat(match_v, ' is first and has VA ', VA, ' in t2') AS EXPLANATION
FROM ranked
WHERE rn = 1
ORDER BY PL;

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