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 -- 明细
sql计算‘去年同期’的问题,求一优化方案,谢谢。
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
1条回答 默认 最新
- save4me 2016-01-11 09:12关注
你看你的这些子查询的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 -- 明细
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 1无用
悬赏问题
- ¥15 NAO机器人的录音程序保存问题
- ¥15 C#读写EXCEL文件,不同编译
- ¥15 MapReduce结果输出到HBase,一直连接不上MySQL
- ¥15 扩散模型sd.webui使用时报错“Nonetype”
- ¥15 stm32流水灯+呼吸灯+外部中断按键
- ¥15 将二维数组,按照假设的规定,如0/1/0 == "4",把对应列位置写成一个字符并打印输出该字符
- ¥15 NX MCD仿真与博途通讯不了啥情况
- ¥15 win11家庭中文版安装docker遇到Hyper-V启用失败解决办法整理
- ¥15 gradio的web端页面格式不对的问题
- ¥15 求大家看看Nonce如何配置