sqlite查询对子查询的结果做简单的计算

Question

我有这个查询需要在 sqlite (iOS) 上执行。

SELECT k.ZKUNDENNR, k.ZNAME1,
(SELECT SUM(vk.ZNETTO) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2013 AND ZMONAT>=1 AND ZMONAT<=6) AS summeJ,
(SELECT SUM(vk.ZNETTO) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2012 AND ZMONAT>=1 AND ZMONAT<=6) as summeVJ,
(SELECT SUM(vk.ZDB_BASIS) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2012 AND ZMONAT>=1 AND ZMONAT<=6) as summeDBVJ,
(SELECT SUM(vk.ZDB_BASIS) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2013 AND ZMONAT>=1 AND ZMONAT<=6) as summeDBJ, 
((summeJ-summeVJ)/summeVJ*100) AS variance FROM ZKUNDE k WHERE summeJ>0 ORDER BY summeJ DESC LIMIT 0,10

我需要做的是计算 summeJ之间的方差和 summeVJ ，但我总是收到此错误:“没有这样的列:summeJ”。有人可以帮我吗？
提前致谢

Answer 1

需要使用子查询，因为列别名不能在select的同级重用:

SELECT t.*, ((summeJ-summeVJ)/summeVJ*100) AS variance
FROM (SELECT k.ZKUNDENNR, k.ZNAME1,
             (SELECT SUM(vk.ZNETTO) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2013 AND ZMONAT>=1 AND ZMONAT<=6) AS summeJ,
             (SELECT SUM(vk.ZNETTO) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2012 AND ZMONAT>=1 AND ZMONAT<=6) as summeVJ,
             (SELECT SUM(vk.ZDB_BASIS) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2012 AND ZMONAT>=1 AND ZMONAT<=6) as summeDBVJ,
             (SELECT SUM(vk.ZDB_BASIS) FROM ZPBSROW r LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=r.Z_PK WHERE r.ZKUNDE=k.Z_PK AND ZJAHR=2013 AND ZMONAT>=1 AND ZMONAT<=6) as summeDBJ
      FROM ZKUNDE k
     ) t
WHERE summeJ>0
ORDER BY summeJ DESC
LIMIT 0,10