基于SELECT生成的计算列条件关联两表计算交易估值变动的SQL问题

SQL 同查询层级生成的计算列无法直接用于关联操作，你可以通过以下3种常用方式解决，同时已修正你原CASE表达式中不符合需求规则的逻辑错误： #

方案1：使用CTE（公用表表达式）封装计算结果（最常用，代码可读性高） #

WITH transaction_cal AS (
    SELECT 
        TradeID,
        `Purchase Date`,
        `Sell Date`,
        -- 修正后的估值起始日逻辑
        CASE
            WHEN `Purchase Date` < '2020-01-01' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-01-01'
            WHEN `Purchase Date` < '2020-01-01' AND `Sell Date` <= '2020-12-31' THEN '2020-01-01'
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN `Purchase Date`
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND `Sell Date` <= '2020-12-31' THEN `Purchase Date`
        END AS `Start Date`,
        -- 修正后的估值结束日逻辑
        CASE
            WHEN `Purchase Date` < '2020-01-01' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-12-31'
            WHEN `Purchase Date` < '2020-01-01' AND `Sell Date` <= '2020-12-31' THEN `Sell Date`
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-12-31'
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND `Sell Date` <= '2020-12-31' THEN `Sell Date`
        END AS `End Date`
    FROM Transaction_Table
)
SELECT 
    tc.TradeID,
    tc.`Purchase Date`,
    tc.`Sell Date`,
    tc.`Start Date`,
    tc.`End Date`,
    v_start.Valuation AS 期初估值,
    v_end.Valuation AS 期末估值,
    (v_end.Valuation - v_start.Valuation) AS 估值变动额
FROM transaction_cal tc
-- 关联取期初估值
LEFT JOIN Valuation_Table v_start 
    ON tc.TradeID = v_start.`Trade ID` 
    AND tc.`Start Date` = v_start.`Valuation Date`
-- 关联取期末估值
LEFT JOIN Valuation_Table v_end 
    ON tc.TradeID = v_end.`Trade ID` 
    AND tc.`End Date` = v_end.`Valuation Date`

方案2：使用子查询封装计算结果 #

逻辑和CTE一致，适合不支持CTE的低版本数据库：

SELECT 
    tc.TradeID,
    tc.`Purchase Date`,
    tc.`Sell Date`,
    tc.`Start Date`,
    tc.`End Date`,
    v_start.Valuation AS 期初估值,
    v_end.Valuation AS 期末估值,
    (v_end.Valuation - v_start.Valuation) AS 估值变动额
FROM (
    SELECT 
        TradeID,
        `Purchase Date`,
        `Sell Date`,
        CASE
            WHEN `Purchase Date` < '2020-01-01' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-01-01'
            WHEN `Purchase Date` < '2020-01-01' AND `Sell Date` <= '2020-12-31' THEN '2020-01-01'
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN `Purchase Date`
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND `Sell Date` <= '2020-12-31' THEN `Purchase Date`
        END AS `Start Date`,
        CASE
            WHEN `Purchase Date` < '2020-01-01' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-12-31'
            WHEN `Purchase Date` < '2020-01-01' AND `Sell Date` <= '2020-12-31' THEN `Sell Date`
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-12-31'
            WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND `Sell Date` <= '2020-12-31' THEN `Sell Date`
        END AS `End Date`
    FROM Transaction_Table
) tc
LEFT JOIN Valuation_Table v_start 
    ON tc.TradeID = v_start.`Trade ID` 
    AND tc.`Start Date` = v_start.`Valuation Date`
LEFT JOIN Valuation_Table v_end 
    ON tc.TradeID = v_end.`Trade ID` 
    AND tc.`End Date` = v_end.`Valuation Date`

方案3：使用临时表存储计算结果（适合大数据量场景，性能更优） #

-- 生成临时表存储带计算列的交易数据
CREATE TEMPORARY TABLE temp_transaction_cal AS
SELECT 
    TradeID,
    `Purchase Date`,
    `Sell Date`,
    CASE
        WHEN `Purchase Date` < '2020-01-01' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-01-01'
        WHEN `Purchase Date` < '2020-01-01' AND `Sell Date` <= '2020-12-31' THEN '2020-01-01'
        WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN `Purchase Date`
        WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND `Sell Date` <= '2020-12-31' THEN `Purchase Date`
    END AS `Start Date`,
    CASE
        WHEN `Purchase Date` < '2020-01-01' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-12-31'
        WHEN `Purchase Date` < '2020-01-01' AND `Sell Date` <= '2020-12-31' THEN `Sell Date`
        WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND (`Sell Date` IS NULL OR `Sell Date` > '2020-12-31') THEN '2020-12-31'
        WHEN `Purchase Date` BETWEEN '2020-01-01' AND '2020-12-31' AND `Sell Date` <= '2020-12-31' THEN `Sell Date`
    END AS `End Date`
FROM Transaction_Table;

-- 关联估值表取数
SELECT 
    tc.TradeID,
    tc.`Purchase Date`,
    tc.`Sell Date`,
    tc.`Start Date`,
    tc.`End Date`,
    v_start.Valuation AS 期初估值,
    v_end.Valuation AS 期末估值,
    (v_end.Valuation - v_start.Valuation) AS 估值变动额
FROM temp_transaction_cal tc
LEFT JOIN Valuation_Table v_start 
    ON tc.TradeID = v_start.`Trade ID` 
    AND tc.`Start Date` = v_start.`Valuation Date`
LEFT JOIN Valuation_Table v_end 
    ON tc.TradeID = v_end.`Trade ID` 
    AND tc.`End Date` = v_end.`Valuation Date`;

-- 用完可删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_transaction_cal;

注意：如果你的数据库日期格式为日/月/年，可将代码中的'2020-01-01'替换为'01/01/2020'、'2020-12-31'替换为'31/12/2020'即可。

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