如何在Sybase中实现SELECT语句内嵌套SELECT查询？

你的原始SQL写法不符合Sybase语法规范，不能直接在SELECT列表中嵌入完整的多表联合查询。以下是两种可行的实现方式：

方式一：标量子查询（直接嵌入SELECT列表） #

将聚合查询作为标量子查询放在SELECT字段列表中，因为SUM()函数会返回单一值，符合标量子查询的要求：

select 
    col1, 
    col2,
    (
        SELECT SUM(Rate_Index_Value) 
        FROM ( 
            select Rate_Index_Value 
            from LOAN 
            where Loan_ID = 17781 and Processing_End_Date > '9999' 
              and Business_End_Date > '9999'
            UNION ALL
            select (BrokerDealer_Split + LendingBank_Split) AS Rate_Index_Value 
            from LOAN_PARTY 
            where Loan_ID = 17781 and Processing_End_Date > '9999' 
              and Business_End_Date > '9999' 
        ) LOAN_AND_LOAN_PARITY
    ) AS Original_Interest_Rate,
    col3,
    col4
from table a
where cond1 = cond1

方式二：交叉连接聚合结果表 #

将聚合查询的结果作为临时表，通过CROSS JOIN和主表关联，适合需要多次复用聚合结果的场景：

select 
    a.col1, 
    a.col2,
    agg.Original_Interest_Rate,
    a.col3,
    a.col4
from table a
cross join (
    SELECT SUM(Rate_Index_Value) AS Original_Interest_Rate 
    FROM ( 
        select Rate_Index_Value 
        from LOAN 
        where Loan_ID = 17781 and Processing_End_Date > '9999' 
          and Business_End_Date > '9999'
        UNION ALL
        select (BrokerDealer_Split + LendingBank_Split) AS Rate_Index_Value 
        from LOAN_PARTY 
        where Loan_ID = 17781 and Processing_End_Date > '9999' 
          and Business_End_Date > '9999' 
    ) LOAN_AND_LOAN_PARITY
) agg
where a.cond1 = cond1

扩展：关联子查询（动态匹配主表字段） #

如果主表table a包含Loan_ID字段，需要为每一行主表数据计算对应Loan_ID的聚合值，可以使用关联子查询：

select 
    col1, 
    col2,
    (
        SELECT SUM(Rate_Index_Value) 
        FROM ( 
            select Rate_Index_Value 
            from LOAN 
            where Loan_ID = a.Loan_ID and Processing_End_Date > '9999' 
              and Business_End_Date > '9999'
            UNION ALL
            select (BrokerDealer_Split + LendingBank_Split) AS Rate_Index_Value 
            from LOAN_PARTY 
            where Loan_ID = a.Loan_ID and Processing_End_Date > '9999' 
              and Business_End_Date > '9999' 
        ) LOAN_AND_LOAN_PARITY
    ) AS Original_Interest_Rate,
    col3,
    col4
from table a
where cond1 = cond1

注意事项 #

• 标量子查询必须保证返回唯一一行一列，SUM()聚合函数天然满足这个条件
• 交叉连接会将聚合结果与主表的每一行数据配对，适合固定聚合值的场景
• 关联子查询会根据主表的每行数据动态计算聚合值，适合需要按主表字段过滤的场景

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