求助大佬帮忙写一下科目余额表的查询语句

[lascas]

本人最近在开发一个审计软件。
遇到的问题是科目余额表查询。
求助大佬们给个指点。
科目余额表（表名：ZZ）的表结构如下：
KJQID KM_CodeID JFFS DFFS JFYE DFYE
0 1001 0￥ 0￥ 1,226.02￥ 0￥
99 1001 549￥ 1766.02￥ 9￥ 0￥

KJQID(会计期ID，KJQID有0，99，1-12，KJQID 为0时代表期初余额，分为借方余额和贷方余额，KJQID为99时代表期末余额)
KM_CodeID 科目代码
JFFS 本期借方发生
DFFS 本期贷方发生
JFYE 借方余额，既可以是期初余额 又可以是期末余额
如上面的例子，1001为现金，期初借方金额1226，本期入549 出1766.02，剩9元
现在想根据KM_CodeID 进行展示，展示的结构为：
科目代码(KM_CodeID) 期初借方(JFYE) 期初贷方（DFYE） 本期借方（JFFS） 本期贷方(DFFS) 期末借方（JFYE） 期末贷方（DFYE）

本人写的代码为（加了个科目名称表KM）：
Select KM.KM_CodeID,KM.sName,A.JFYE,A.DFYE,JFFS,DFFS,B.JFYE,B.DFYE from KM, (SELECT ZZ.KM_CodeID as ID, ZZ.JFYE, ZZ.DFYE
FROM ZZ
WHERE ZZ.BZID="CNY" AND ZZ.SJBZ="*" AND ZZ.KJQID=0 AND Len([ZZ].[KM_CodeID])=4) as A,
(SELECT ZZ.KM_CodeID as ID, ZZ.JFFS, ZZ.DFFS, ZZ.JFYE, ZZ.DFYE
FROM ZZ
WHERE ZZ.KJQID=99 AND ZZ.BZID="CNY" AND ZZ.SJBZ="*" AND Len([ZZ].[KM_CodeID])=4) as B where KM.KM_CodeID = A.ID or KM.KM_CodeID = B.ID
这样出来的表要么有很多重复的项目并且有些数据对不上，如果将 KM.KM_CodeID = A.ID or KM.KM_CodeID = B.ID 换成and，又会显示不完全科目。
求大佬给个指点。

[lascas]

[RINK_1]

SELECT KM_CODEID,QCJF,QCDF,BQJF,BQDF,
CASE WHEN QMYE>=0 THEN BQDF ELSE 0 END AS JFYE,
CASE WHEN QMYE<0 THEN ABS(BQDF) ELSE 0 END AS DFYE
FROM
(SELECT KM_CODEID,
SUM(CASE WHEN KJQID=0 THEN JFYE ELSE 0 END) AS QCJF,
SUM(CASE WHEN KJQID=0 THEN DFYE ELSE 0 END) AS QCDF,
SUM(CASE WHEN KJQID NOT IN (0,99) THEN JFFS ELSE 0 END) AS BQJF,
SUM(CASE WHEN KJQID NOT IN (0,99) THEN DFFS ELSE 0 END) AS BQDF,
SUM(CASE WHEN KJQID=0 THEN JFYE ELSE 0 END)+
SUM(CASE WHEN KJQID NOT IN (0,99) THEN JFFS ELSE 0 END)-
SUM(CASE WHEN KJQID=0 THEN DFYE ELSE 0 END)-
SUM(CASE WHEN KJQID NOT IN (0,99) THEN DFFS ELSE 0 END) AS QMYE
FROM ZZ
GROUP BY KM_CODEID) AS A

[lascas]

引用 2 楼 RINK_1 的回复:

SELECT KM_CODEID,QCJF,QCDF,BQJF,BQDF,
CASE WHEN QMYE>=0 THEN BQDF ELSE 0 END AS JFYE,
CASE WHEN QMYE<0 THEN ABS(BQDF) ELSE 0 END AS DFYE
FROM
(SELECT KM_CODEID,
SUM(CASE WHEN KJQID=0 THEN JFYE ELSE 0 END) AS QCJF,
SUM(CASE WHEN KJQID=0 THEN DFYE ELSE 0 END) AS QCDF,
SUM(CASE WHEN KJQID NOT IN (0,99) THEN JFFS ELSE 0 END) AS BQJF,
SUM(CASE WHEN KJQID NOT IN (0,99) THEN DFFS ELSE 0 END) AS BQDF,
SUM(CASE WHEN KJQID=0 THEN JFYE ELSE 0 END)+
SUM(CASE WHEN KJQID NOT IN (0,99) THEN JFFS ELSE 0 END)-
SUM(CASE WHEN KJQID=0 THEN DFYE ELSE 0 END)-
SUM(CASE WHEN KJQID NOT IN (0,99) THEN DFFS ELSE 0 END) AS QMYE
FROM ZZ
GROUP BY KM_CODEID) AS A

感谢大佬给予指点 想做出来是这种样式，就是会计期为0的表和会计期为99（期末）的表平行就可以。 我感觉您上面写的SQL语句好像是跟我所想要的不大一样，审计软件不能自己加减借贷，只能取数，不过给了思路，非常感谢。
http://www.laiweicpa.com/wp-content/uploads/2018/10/2-3.png

[lascas]

引用 2 楼 RINK_1 的回复:

SELECT KM_CODEID,QCJF,QCDF,BQJF,BQDF,
CASE WHEN QMYE>=0 THEN BQDF ELSE 0 END AS JFYE,
CASE WHEN QMYE<0 THEN ABS(BQDF) ELSE 0 END AS DFYE
FROM
(SELECT KM_CODEID,
SUM(CASE WHEN KJQID=0 THEN JFYE ELSE 0 END) AS QCJF,
SUM(CASE WHEN KJQID=0 THEN DFYE ELSE 0 END) AS QCDF,
SUM(CASE WHEN KJQID NOT IN (0,99) THEN JFFS ELSE 0 END) AS BQJF,
SUM(CASE WHEN KJQID NOT IN (0,99) THEN DFFS ELSE 0 END) AS BQDF,
SUM(CASE WHEN KJQID=0 THEN JFYE ELSE 0 END)+
SUM(CASE WHEN KJQID NOT IN (0,99) THEN JFFS ELSE 0 END)-
SUM(CASE WHEN KJQID=0 THEN DFYE ELSE 0 END)-
SUM(CASE WHEN KJQID NOT IN (0,99) THEN DFFS ELSE 0 END) AS QMYE
FROM ZZ
GROUP BY KM_CODEID) AS A

根据大佬您的写法，我自己修改了一下，写出来了。非常感谢您的帮助！！！谢谢！
我的代码是这样的，Access版本：
SELECT KM_CodeID,sum(iif(ZZ.KJQID=0,ZZ.JFYE,0)) AS QCJF,sum(iif(ZZ.KJQID=0,ZZ.DFYE,0)) AS QCDF,sum(iif(ZZ.KJQID=99,ZZ.JFFS,0)) AS BQJF,sum(iif(ZZ.KJQID=99,ZZ.DFFS,0)) AS BQDF,sum(iif(ZZ.KJQID=99,ZZ.JFYE,0)) AS QMJF,sum(iif(ZZ.KJQID=0,ZZ.DFYE,0)) AS DFYE
FROM ZZ WHERE SJBZ='1' AND len(KM_CodeID)='4' GROUP BY KM_CodeID;