在项目过程中遇到的问题,需要对于一张库表内的数据,用sql语句进行处理计算,完成计算操作得出结果。在查询资料之后自己进行调整后得出完整的过程方法。分为两步操作:
--1、同一个表进行增量计算
SELECT "a"."c0","a"."c1",("a"."c3" - "b"."c3") as czrkzl,("a"."c4" - "b"."c4") as hjrkzl
from "reportdataAll_2730" AS "a", "reportdataAll_2730" AS "b"
where "a"."c0"="b"."c0"+1 AND "a"."c1"="b"."c1";
--2、根据筛选表,通过需要关联的字段对原表进行关联,得出结果表
SELECT "t1"."id","t1"."updatetime","t1"."c0","t1"."c1","t1"."c2","t1"."c3","t1"."c4","t1"."c5","t1"."c6",
"t1"."c7","t2"."czrkzl","t2"."hjrkzl" FROM "reportdataAll_2730" AS "t1"
LEFT JOIN (SELECT "a"."c0","a"."c1",("a"."c3" - "b"."c3") as "czrkzl",("a"."c4" - "b"."c4") as "hjrkzl"
from "reportdataAll_2730" AS "a", "reportdataAll_2730" AS "b" where "a"."c0"="b"."c0"+1 AND "a"."c1"="b"."c1") AS "t2"
ON "t1"."c0"="t2"."c0" and "t1"."c1"="t2"."c1"