zhushuhai331
人生有酒多忘欢
2016-01-11 08:45

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

  • 去年同期
  • 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 -- 明细

图片说明

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答