SELECT A.TELLER_NO,B.TELLER_NO,
SUM(DECODE(A.SLIP_LEVEL, 1, 1, 0)) AS "差错",
SUM(DECODE(A.SLIP_LEVEL, 2, 1, 0)) AS "违规",
SUM(DECODE(A.SLIP_LEVEL, 3, 1, 0)) AS "案件",
SUM(DECODE(B.SLIP_LEVEL, 1, 1, 0)) AS "一类差错",
SUM(DECODE(B.SLIP_LEVEL, 2, 1, 0)) AS "二类差错",
SUM(DECODE(B.SLIP_LEVEL, 3, 1, 0)) AS "三类差错"
FROM (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'YJD') A
FULL JOIN (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'CCD') B
ON A.TELLER_NO = B.TELLER_NO
GROUP BY A.TELLER_NO,B.TELLER_NO;出来的结果1 A0440 1 0 1 0 0 0
2 A0651 0 0 0 1 0 0
3 A0648 A0648 0 1 0 0 0 1
4 J0059 0 1 0 0 0 0
5 A0721 0 0 0 0 0 1
有没有办法让TELLER_NO结合到一起?
SUM(DECODE(A.SLIP_LEVEL, 1, 1, 0)) AS "差错",
SUM(DECODE(A.SLIP_LEVEL, 2, 1, 0)) AS "违规",
SUM(DECODE(A.SLIP_LEVEL, 3, 1, 0)) AS "案件",
SUM(DECODE(B.SLIP_LEVEL, 1, 1, 0)) AS "一类差错",
SUM(DECODE(B.SLIP_LEVEL, 2, 1, 0)) AS "二类差错",
SUM(DECODE(B.SLIP_LEVEL, 3, 1, 0)) AS "三类差错"
FROM (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'YJD') A
FULL JOIN (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'CCD') B
ON A.TELLER_NO = B.TELLER_NO
GROUP BY A.TELLER_NO,B.TELLER_NO;出来的结果1 A0440 1 0 1 0 0 0
2 A0651 0 0 0 1 0 0
3 A0648 A0648 0 1 0 0 0 1
4 J0059 0 1 0 0 0 0
5 A0721 0 0 0 0 0 1
有没有办法让TELLER_NO结合到一起?
SUM(DECODE(A.SLIP_LEVEL, 1, 1, 0)) AS "差错",
SUM(DECODE(A.SLIP_LEVEL, 2, 1, 0)) AS "违规",
SUM(DECODE(A.SLIP_LEVEL, 3, 1, 0)) AS "案件",
SUM(DECODE(B.SLIP_LEVEL, 1, 1, 0)) AS "一类差错",
SUM(DECODE(B.SLIP_LEVEL, 2, 1, 0)) AS "二类差错",
SUM(DECODE(B.SLIP_LEVEL, 3, 1, 0)) AS "三类差错"
FROM (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'YJD') A
FULL JOIN (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'CCD') B
ON A.TELLER_NO = B.TELLER_NO
GROUP BY A.TELLER_NO
SUM(DECODE(A.SLIP_LEVEL, 1, 1, 0)) AS "差错",
SUM(DECODE(A.SLIP_LEVEL, 2, 1, 0)) AS "违规",
SUM(DECODE(A.SLIP_LEVEL, 3, 1, 0)) AS "案件",
SUM(DECODE(B.SLIP_LEVEL, 1, 1, 0)) AS "一类差错",
SUM(DECODE(B.SLIP_LEVEL, 2, 1, 0)) AS "二类差错",
SUM(DECODE(B.SLIP_LEVEL, 3, 1, 0)) AS "三类差错"
FROM (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'YJD') A
FULL JOIN (SELECT TELLER_NO, SLIP_LEVEL FROM ET_BUSIFORM_TB WHERE SUBSTR(FORM_ID, 1, 3) = 'CCD') B
ON A.TELLER_NO = B.TELLER_NO
GROUP BY A.TELLER_NO
2 A0651 0 0 0 1 0 0
3 A0648 0 1 0 0 1 1
4 J0059 0 1 0 0 0 0
5 A0721 0 0 0 0 1 1SELECT DECODE(TELLER_NO1, NULL, TELLER_NO2, TELLER_NO1) AS TELLER_NO,
TYPE1 AS "差错",
TYPE2 AS "违规",
TYPE3 AS "案件",
LEVEL1 AS "一类差错",
LEVEL3 AS "二类差错",
LEVEL3 AS "三类差错"
FROM (SELECT A.TELLER_NO AS TELLER_NO1,
B.TELLER_NO AS TELLER_NO2,
SUM(DECODE(A.SLIP_LEVEL, 1, 1, 0)) AS TYPE1,
SUM(DECODE(A.SLIP_LEVEL, 2, 1, 0)) AS TYPE2,
SUM(DECODE(A.SLIP_LEVEL, 3, 1, 0)) AS TYPE3,
SUM(DECODE(B.SLIP_LEVEL, 1, 1, 0)) AS LEVEL1,
SUM(DECODE(B.SLIP_LEVEL, 2, 1, 0)) AS LEVEL2,
SUM(DECODE(B.SLIP_LEVEL, 3, 1, 0)) AS LEVEL3
FROM (SELECT TELLER_NO, SLIP_LEVEL
FROM ET_BUSIFORM_TB
WHERE SUBSTR(FORM_ID, 1, 3) = 'YJD' ) A
FULL JOIN (SELECT TELLER_NO, SLIP_LEVEL
FROM ET_BUSIFORM_TB
WHERE SUBSTR(FORM_ID, 1, 3) = 'CCD' ) B
ON A.TELLER_NO = B.TELLER_NO
GROUP BY A.TELLER_NO, B.TELLER_NO);