能否通过SQL将左表转换为右表？First CVN对应最低分，Last CVN对应最高分

完全可以通过SQL实现这类从多行明细到单行聚合的转换，核心思路是按trq_id分组，提取每组中对应最低分/最高分的CVN编号。结合你提供的查询语句，以下是具体实现方案：

前提说明 #

根据描述，First cvn是对应最低分的CVN，Last cvn是对应最高分的CVN。这里默认/TMCSL/CVN_NUM本身可作为排序依据（若有单独的分数字段，替换排序字段即可）。

方案一：使用窗口函数（推荐，效率更高） #

利用ROW_NUMBER()窗口函数为每个trq_id下的CVN按分数（或CVN_NUM）排序，再通过条件聚合得到目标结构：

WITH ranked_data AS (
    SELECT 
        st.trq_id,      
        sdpl."/TMCSL/CVN_NUM" AS cvn_num,
        sp1."STOP_ID",
        -- 升序排取第一为First cvn（对应最低分）
        ROW_NUMBER() OVER (PARTITION BY st.trq_id ORDER BY sdpl."/TMCSL/CVN_NUM" ASC) AS rn_first,
        -- 降序排取第一为Last cvn（对应最高分）
        ROW_NUMBER() OVER (PARTITION BY st.trq_id ORDER BY sdpl."/TMCSL/CVN_NUM" DESC) AS rn_last
    FROM SAPHANADB."/SCMTMS/D_TRQROT" AS st
    INNER JOIN SAPHANADB."/SCMTMS/D_TORITE" AS s2 ON
        s2.trq_id = st.trq_id
        AND s2.item_type = 'CONT'
        AND s2.item_cat = 'TUR'
        AND s2.provision_req <> 'P'
        AND s2.empty_return_req <> 'R'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORROT" AS sd2 ON
        sd2.db_key = s2.parent_key
        AND sd2.tor_cat = 'TU'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORSTP" AS sp1 ON
        sd2.db_key = sp1.parent_key
        AND sp1.stop_cat = 'O'
        AND sp1.log_locid <> ''
        AND (
            sp1."/TMCSL/LOC_ROLE" = '3'
            OR sp1."/TMCSL/LOC_ROLE" = '5'
            OR sp1."/TMCSL/LOC_ROLE" = '7'
        )
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDPL" AS sdpl ON
        sdpl.db_key = sp1."/TMCSL/ASGN_SCHED_STOP_KEY"
        AND sdpl."/TMCSL/CVN_NUM" <> ''
        AND sdpl.del_flag = ''
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDEP" AS sdep ON
        sdep.db_key = sdpl.parent_key
        AND sdep.del_flag = ''
        AND sdep."/TMCSL/VOYAGE_TYPE" = 'EX'
)
SELECT 
    trq_id,
    MAX(CASE WHEN rn_first = 1 THEN cvn_num END) AS first_cvn,
    MAX(CASE WHEN rn_last = 1 THEN cvn_num END) AS last_cvn,
    MAX(STOP_ID) AS stop_id -- 若每个trq_id对应唯一STOP_ID，用MAX/MIN均可
FROM ranked_data
GROUP BY trq_id;

方案二：分组聚合后关联子查询 #

如果不使用窗口函数，可先分组获取每个trq_id的最低/最高CVN值，再关联原数据提取对应编号：

WITH cvn_extremes AS (
    SELECT 
        st.trq_id,
        MIN(sdpl."/TMCSL/CVN_NUM") AS min_cvn,
        MAX(sdpl."/TMCSL/CVN_NUM") AS max_cvn,
        MAX(sp1."STOP_ID") AS stop_id
    FROM SAPHANADB."/SCMTMS/D_TRQROT" AS st
    INNER JOIN SAPHANADB."/SCMTMS/D_TORITE" AS s2 ON
        s2.trq_id = st.trq_id
        AND s2.item_type = 'CONT'
        AND s2.item_cat = 'TUR'
        AND s2.provision_req <> 'P'
        AND s2.empty_return_req <> 'R'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORROT" AS sd2 ON
        sd2.db_key = s2.parent_key
        AND sd2.tor_cat = 'TU'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORSTP" AS sp1 ON
        sd2.db_key = sp1.parent_key
        AND sp1.stop_cat = 'O'
        AND sp1.log_locid <> ''
        AND (
            sp1."/TMCSL/LOC_ROLE" = '3'
            OR sp1."/TMCSL/LOC_ROLE" = '5'
            OR sp1."/TMCSL/LOC_ROLE" = '7'
        )
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDPL" AS sdpl ON
        sdpl.db_key = sp1."/TMCSL/ASGN_SCHED_STOP_KEY"
        AND sdpl."/TMCSL/CVN_NUM" <> ''
        AND sdpl.del_flag = ''
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDEP" AS sdep ON
        sdep.db_key = sdpl.parent_key
        AND sdep.del_flag = ''
        AND sdep."/TMCSL/VOYAGE_TYPE" = 'EX'
    GROUP BY st.trq_id
)
SELECT 
    ce.trq_id,
    fc.cvn_num AS first_cvn,
    lc.cvn_num AS last_cvn,
    ce.stop_id
FROM cvn_extremes ce
-- 关联获取最低分对应的CVN
LEFT JOIN (
    SELECT st.trq_id, sdpl."/TMCSL/CVN_NUM" AS cvn_num
    FROM SAPHANADB."/SCMTMS/D_TRQROT" AS st
    INNER JOIN SAPHANADB."/SCMTMS/D_TORITE" AS s2 ON
        s2.trq_id = st.trq_id
        AND s2.item_type = 'CONT'
        AND s2.item_cat = 'TUR'
        AND s2.provision_req <> 'P'
        AND s2.empty_return_req <> 'R'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORROT" AS sd2 ON
        sd2.db_key = s2.parent_key
        AND sd2.tor_cat = 'TU'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORSTP" AS sp1 ON
        sd2.db_key = sp1.parent_key
        AND sp1.stop_cat = 'O'
        AND sp1.log_locid <> ''
        AND (
            sp1."/TMCSL/LOC_ROLE" = '3'
            OR sp1."/TMCSL/LOC_ROLE" = '5'
            OR sp1."/TMCSL/LOC_ROLE" = '7'
        )
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDPL" AS sdpl ON
        sdpl.db_key = sp1."/TMCSL/ASGN_SCHED_STOP_KEY"
        AND sdpl."/TMCSL/CVN_NUM" <> ''
        AND sdpl.del_flag = ''
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDEP" AS sdep ON
        sdep.db_key = sdpl.parent_key
        AND sdep.del_flag = ''
        AND sdep."/TMCSL/VOYAGE_TYPE" = 'EX'
) fc ON ce.trq_id = fc.trq_id AND fc.cvn_num = ce.min_cvn
-- 关联获取最高分对应的CVN
LEFT JOIN (
    SELECT st.trq_id, sdpl."/TMCSL/CVN_NUM" AS cvn_num
    FROM SAPHANADB."/SCMTMS/D_TRQROT" AS st
    INNER JOIN SAPHANADB."/SCMTMS/D_TORITE" AS s2 ON
        s2.trq_id = st.trq_id
        AND s2.item_type = 'CONT'
        AND s2.item_cat = 'TUR'
        AND s2.provision_req <> 'P'
        AND s2.empty_return_req <> 'R'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORROT" AS sd2 ON
        sd2.db_key = s2.parent_key
        AND sd2.tor_cat = 'TU'
    INNER JOIN SAPHANADB."/SCMTMS/D_TORSTP" AS sp1 ON
        sd2.db_key = sp1.parent_key
        AND sp1.stop_cat = 'O'
        AND sp1.log_locid <> ''
        AND (
            sp1."/TMCSL/LOC_ROLE" = '3'
            OR sp1."/TMCSL/LOC_ROLE" = '5'
            OR sp1."/TMCSL/LOC_ROLE" = '7'
        )
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDPL" AS sdpl ON
        sdpl.db_key = sp1."/TMCSL/ASGN_SCHED_STOP_KEY"
        AND sdpl."/TMCSL/CVN_NUM" <> ''
        AND sdpl.del_flag = ''
    INNER JOIN SAPHANADB."/SCMTMS/D_SCHDEP" AS sdep ON
        sdep.db_key = sdpl.parent_key
        AND sdep.del_flag = ''
        AND sdep."/TMCSL/VOYAGE_TYPE" = 'EX'
) lc ON ce.trq_id = lc.trq_id AND lc.cvn_num = ce.max_cvn;

注意事项 #

• 如果“最低分/最高分”基于单独的分数字段而非CVN_NUM，只需将排序条件中的sdpl."/TMCSL/CVN_NUM"替换为对应分数字段即可。
• 若同一trq_id下有多个CVN对应相同的最低/最高分，ROW_NUMBER()会随机取其中一个；如需保留所有符合条件的CVN，可改用RANK()或DENSE_RANK()并调整聚合逻辑。

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