表一 (ywba)
ywbaid jgid ywlxid nsrbh sfje
y001 1001 1000001 a0001 1.00
y002 1001 1000001 a0002 2.00
y003 1002 1000001 b0001 1.00
y004 1001 1000002 b0001 3.00
表二 (ywlx)
ywlxid mc
1000001 税前扣除
1000002 汇算清剿
1000003 其它
表三 (ywsjlx)
ywsjlx mc ywlxid
1 纳税调整增加额 1000001
2 纳税调整减少额 1000001
3 应补税额 1000002
4 应退税额 1000002
5 应退税额 1000003
表四 (ywsj)
id ywbaid ywsjlxid value
1 y001 1 10
2 y001 2 20
3 y002 1 30
4 y002 2 10
5 y003 5 2
6 y004 3 0
7 y004 4 5求汇总
税前扣除 汇算清剿 其它
不重复 不重复 和 不重复 不重复 和 不重复 不重复 和
总数jgid 总nsrbh 总sfje 总数jgid 总nsrbh 总sfje 纳税调整增加额 纳税调整减少额 总数jgid 总nsrbh 总sfje 应补税额 应退税额 ---------------
4 3 7 2 3 4 40 30 1 1 3 0 5
ywbaid jgid ywlxid nsrbh sfje
y001 1001 1000001 a0001 1.00
y002 1001 1000001 a0002 2.00
y003 1002 1000001 b0001 1.00
y004 1001 1000002 b0001 3.00
表二 (ywlx)
ywlxid mc
1000001 税前扣除
1000002 汇算清剿
1000003 其它
表三 (ywsjlx)
ywsjlx mc ywlxid
1 纳税调整增加额 1000001
2 纳税调整减少额 1000001
3 应补税额 1000002
4 应退税额 1000002
5 应退税额 1000003
表四 (ywsj)
id ywbaid ywsjlxid value
1 y001 1 10
2 y001 2 20
3 y002 1 30
4 y002 2 10
5 y003 5 2
6 y004 3 0
7 y004 4 5求汇总
税前扣除 汇算清剿 其它
不重复 不重复 和 不重复 不重复 和 不重复 不重复 和
总数jgid 总nsrbh 总sfje 总数jgid 总nsrbh 总sfje 纳税调整增加额 纳税调整减少额 总数jgid 总nsrbh 总sfje 应补税额 应退税额 ---------------
4 3 7 2 3 4 40 30 1 1 3 0 5
ywbaid jgid ywlxid nsrbh sfje
y001 1001 1000001 a0001 1.00
y002 1001 1000001 a0002 2.00
y003 1002 1000001 b0001 1.00
y004 1001 1000002 b0001 3.00
表二 (ywlx)
ywlxid mc
1000001 税前扣除
1000002 汇算清剿
1000003 其它
表三 (ywsjlx)
ywsjlx mc ywlxid
1 纳税调整增加额 1000001
2 纳税调整减少额 1000001
3 应补税额 1000002
4 应退税额 1000002
5 应退税额 1000003
表四 (ywsj)
id ywbaid ywsjlxid value
1 y001 1 10
2 y001 2 20
3 y002 1 30
4 y002 2 10
5 y003 5 2
6 y004 3 0
7 y004 4 5
税前扣除 汇算清剿 其
总数jgid 总nsrbh 总sfje 总数jgid 总nsrbh 总sfje 纳税调整增加额 纳税调整减少额 总数jgid 总nsrbh 总sfje 应补税额 应退税额 --
4 3 7 2 3 4 40 30 1 1 3 0 5
总数jgid 总nsrbh 总sfje 纳税调整增加额 纳税调整减少额
2 3 4 40 30
表一 (ywba)
ywbaid jgid ywlxid nsrbh sfje
y001 1001 1000001 a0001 1.00
y002 1001 1000001 a0002 2.00
y003 1002 1000001 b0001 1.00
y004 1001 1000002 b0001 3.00 表二 (ywlx)
ywlxid mc
1000001 税前扣除
1000002 汇算清剿
1000003 其它
表三 (ywsjlx)
ywsjlxid mc ywlxid
1 纳税调整增加额 1000001
2 纳税调整减少额 1000001
3 应补税额 1000002
4 应退税额 1000002
5 应退税额 1000003
表四 (ywsj)
id ywbaid ywsjlxid value
1 y001 1 10
2 y001 2 20
3 y002 1 30
4 y002 2 10
5 y003 5 2
6 y004 3 0
7 y004 4 5 其中表三中的ywlxid 是表二中的ywlxid
表四中的ywsjlxid是表三中的ywsjlxid,ywbaid是表一中的ywbaid,
ywbaid 是表一的主健
ywlxid 是表二的主健
ywsjlxid 是表三的主健求这几个表的汇总
税前扣除:
总数jgid 总nsrbh 总sfje 纳税调整增加额 纳税调整减少额
2 3 4 40 30
总数jgid为2 是表一中 ywlxid 为1000001的jgid 的数目即三条 1001,1001,1002,但是不能重复则为2
总nsrbh有三条 a0001 a0002 b0001 三条
总sfje 就是1.00+2.00+1.00=4.00
税前扣除的 ywlxid为1000001 对应的表三中的ywsjlxid有 纳税调整增加额 ,纳税调整减少额 分别为1,2
而1,2和 ywbaid 为y001,y002为分别对应的数值,对于纳税调整增加额1来说有10+30 =40
对于纳税调整减少额2来说有20+10 =30
-------- 测试表
---表1
CREATE TABLE ywba
(
ywbaid VARCHAR2(4),
jgid VARCHAR2(4),
ywlxid VARCHAR2(7),
nsrbh VARCHAR2(5),
sfje NUMBER
);
INSERT INTO ywba VALUES ('y001', '1001', '1000001', 'a0001', 1.00 );
INSERT INTO ywba VALUES ('y002', '1001', '1000001', 'a0002', 2.00 );
INSERT INTO ywba VALUES ('y003', '1002', '1000001', 'b0001', 1.00 );
INSERT INTO ywba VALUES ('y004', '1001', '1000002', 'b0001', 3.00 );
COMMIT;
--- 表2
CREATE TABLE ywlx
(
ywlxid VARCHAR2(7),
mc VARCHAR2(100)
);
INSERT INTO YWLX VALUES ('1000001', '税前扣除');
INSERT INTO YWLX VALUES ('1000002', '汇算清剿');
INSERT INTO YWLX VALUES ('1000003', '其它');
COMMIT;
--- 表3
CREATE TABLE ywsjlx
(
ywsjlxid INTEGER,
mc VARCHAR2(100),
ywlxid VARCHAR2(7)
);
INSERT INTO YWSJLX VALUES(1 ,'纳税调整增加额' ,'1000001');
INSERT INTO YWSJLX VALUES(2 ,'纳税调整减少额' ,'1000001');
INSERT INTO YWSJLX VALUES(3 ,'应补税额' ,'1000002');
INSERT INTO YWSJLX VALUES(4 ,'应退税额' ,'1000002');
INSERT INTO YWSJLX VALUES(5 ,'应退税额' ,'1000003');
COMMIT;
--- 表4
CREATE TABLE ywsj
(
ID INTEGER,
ywbaid VARCHAR2(4),
ywsjlxid INTEGER ,
value NUMBER
);
INSERT INTO YWSJ VALUES (1, 'y001', 1 , 10 );
INSERT INTO YWSJ VALUES (2, 'y001', 2 , 20 );
INSERT INTO YWSJ VALUES (3, 'y002', 1 , 30 );
INSERT INTO YWSJ VALUES (4, 'y002', 2 , 10 );
INSERT INTO YWSJ VALUES (5, 'y003', 5 , 2 );
INSERT INTO YWSJ VALUES (6, 'y004', 3 , 0 );
INSERT INTO YWSJ VALUES (7, 'y004', 4 , 5 );
COMMIT;
-------------------------------------------------------------------------------------
----- 税前扣除 SQL
WITH SQKC_TEMP AS
(
----> WITH 01 找出 税前扣除 的 ywlxid
SELECT ywlxid,MC,1 AS CONNECT_SIGN FROM ywlx
WHERE MC = '税前扣除'
),
YWBA_TEMP AS
(
----> WITH 02 总数jgid 和 总nsrbh 和 总sfje
SELECT COUNT(DISTINCT YWBA.JGID) AS 总数jgid,
COUNT(DISTINCT YWBA.NSRBH) AS 总nsrbh,
SUM(YWBA.SFJE) AS 总sfje,
1 AS CONNECT_SIGN
FROM YWBA,
SQKC_TEMP
WHERE YWBA.YWLXID = SQKC_TEMP.YWLXID
),
ywsj_TEMP1 AS
(
----> WITH 03 纳税调整增加额
SELECT SUM(YWSJ.VALUE) AS 纳税调整增加额,
1 AS CONNECT_SIGN
FROM YWSJ,SQKC_TEMP, ywba,ywsjlx
WHERE YWSJ.YWBAID = YWBA.YWBAID
AND YWSJ.YWSJLXID = YWSJLX.YWSJLXID
AND YWBA.YWLXID = SQKC_TEMP.YWLXID
AND YWSJLX.YWLXID = SQKC_TEMP.YWLXID
AND ywsjlx.mc = '纳税调整增加额'
)
,
ywsj_TEMP2 AS
(
----> WITH 04 纳税调整减少额
SELECT SUM(YWSJ.VALUE) AS 纳税调整减少额,
1 AS CONNECT_SIGN
FROM YWSJ,SQKC_TEMP, ywba,ywsjlx
WHERE YWSJ.YWBAID = YWBA.YWBAID
AND YWSJ.YWSJLXID = YWSJLX.YWSJLXID
AND YWBA.YWLXID = SQKC_TEMP.YWLXID
AND YWSJLX.YWLXID = SQKC_TEMP.YWLXID
AND ywsjlx.mc = '纳税调整减少额'
)---> 最后结果
SELECT R1.MC,R2.总数jgid,R2.总nsrbh,R2.总sfje,R3.纳税调整增加额,R4.纳税调整减少额
FROM SQKC_TEMP R1
LEFT OUTER JOIN YWBA_TEMP R2 ON R1.CONNECT_SIGN = R2.CONNECT_SIGN
LEFT OUTER JOIN ywsj_TEMP1 R3 ON R1.CONNECT_SIGN = R3.CONNECT_SIGN
LEFT OUTER JOIN ywsj_TEMP2 R4 ON R1.CONNECT_SIGN = R4.CONNECT_SIGN
-------------------------------------------------------------------------------------
------查询出来的结果
MC 总数JGID 总NSRBH 总SFJE 纳税调整增加额 纳税调整减少额
税前扣除 2 3 4 40 30