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 gradio的web端页面格式不对的问题
- ¥15 求大家看看Nonce如何配置
- ¥15 Matlab怎么求解含参的二重积分?
- ¥15 苹果手机突然连不上wifi了?
- ¥15 cgictest.cgi文件无法访问
- ¥20 删除和修改功能无法调用
- ¥15 kafka topic 所有分副本数修改
- ¥15 小程序中fit格式等运动数据文件怎样实现可视化?(包含心率信息))
- ¥15 如何利用mmdetection3d中的get_flops.py文件计算fcos3d方法的flops?
- ¥40 串口调试助手打开串口后,keil5的代码就停止了