问题描述

sql计算‘去年同期’的问题，求一优化方案，谢谢。

 SELECT a.period, a.type, a.commodity, a.export, a. CODE, a.unit, a.number, -- 当月量
a.total_number, -- 累积量
a.amount, -- 当月金额
a.total_amount, -- 累计金额
( SELECT number FROM c_now_data b WHERE CAST(b.period AS CHAR(50)) = CAST( CONCAT( LEFT (a.period, 4) - 1, '-', RIGHT (a.period, 2)) AS CHAR (50)) AND b.type = a.type AND b.commodity = a.commodity AND b.export = a.export AND b. CODE = a. CODE ) AS tq_number, -- 当月量（去年同期）
( SELECT total_number FROM c_now_data b WHERE CAST(b.period AS CHAR(50)) = CAST( CONCAT( LEFT (a.period, 4) - 1, '-', RIGHT (a.period, 2)) AS CHAR (50)) AND b.type = a.type AND b.commodity = a.commodity AND b.export = a.export AND b. CODE = a. CODE ) AS tq_total_number, -- 累积量（去年同期）
( SELECT amount FROM c_now_data b WHERE CAST(b.period AS CHAR(50)) = CAST( CONCAT( LEFT (a.period, 4) - 1, '-', RIGHT (a.period, 2)) AS CHAR (50)) AND b.type = a.type AND b.commodity = a.commodity AND b.export = a.export AND b. CODE = a. CODE ) AS tq_amount, -- 当月金额（去年同期）
( SELECT total_amount FROM c_now_data b WHERE CAST(b.period AS CHAR(50)) = CAST( CONCAT( LEFT (a.period, 4) - 1, '-', RIGHT (a.period, 2)) AS CHAR (50)) AND b.type = a.type AND b.commodity = a.commodity AND b.export = a.export AND b. CODE = a. CODE ) AS tq_total_amount -- 累计金额（去年同期）
FROM c_now_data a WHERE a.commodity ='26030000' ORDER BY a.period, -- 日期
a.type, --  类型
a.commodity, -- 产品
a.export, -- 进出口
a. CODE -- 明细

解决方案

你看你的这些子查询的From和Where都是一样的，直接Join

 SELECT a.period,
    a.type,
    a.commodity,
    a.export,
    a.CODE,
    a.unit,
    a.number, -- 当月量
    a.total_number, -- 累积量
    a.amount, -- 当月金额
    a.total_amount, -- 累计金额
    b.number AS tq_number, -- 当月量（去年同期）
    b.total_number AS tq_total_number, -- 累积量（去年同期）
    b.amount AS tq_amount, -- 当月金额（去年同期）
    b.total_amount AS tq_total_amount -- 累计金额（去年同期）
FROM c_now_data a
    LEFT JOIN c_now_data b
    ON CAST(b.period AS CHAR(50)) = CAST( CONCAT( LEFT (a.period, 4) - 1, '-', RIGHT (a.period, 2)) AS CHAR (50))
        AND b.type = a.type
        AND b.commodity = a.commodity
        AND b.export = a.export
        AND b. CODE = a. CODE
WHERE a.commodity ='26030000'
ORDER BY a.period, -- 日期
    a.type, --  类型
    a.commodity, -- 产品
    a.export, -- 进出口
    a. CODE -- 明细