给你个例子看看吧: SELECT A.JGBH, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'01')>0)) Y1RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'01')),0) Y1, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'02')>0)) Y2RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'02')),0) Y2, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'03')>0)) Y3RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'03')),0) Y3, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'04')>0)) Y4RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'04')),0) Y4, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'05')>0)) Y5RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'05')),0) Y5, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'06')>0)) Y6RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'06')),0) Y6, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'07')>0)) Y7RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'07')),0) Y7, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'08')>0)) Y8RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'08')),0) Y8, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'09')>0)) Y9RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'09')),0) Y9, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'10')>0)) Y10RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'10')),0) Y10, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'11')>0)) Y11RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'11')),0) Y11, MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'12')>0)) Y12RS, NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'12')),0) Y12 FROM T_GZFFMX A GROUP BY A.JGBH)
我还没有去认真看,只是看到你的问题想起。你要用的话看看这本书或看看ORACLE的文档。
SELECT A.JGBH,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'01')>0)) Y1RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'01')),0) Y1,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'02')>0)) Y2RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'02')),0) Y2,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'03')>0)) Y3RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'03')),0) Y3,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'04')>0)) Y4RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'04')),0) Y4,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'05')>0)) Y5RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'05')),0) Y5,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'06')>0)) Y6RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'06')),0) Y6,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'07')>0)) Y7RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'07')),0) Y7,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'08')>0)) Y8RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'08')),0) Y8,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'09')>0)) Y9RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'09')),0) Y9,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'10')>0)) Y10RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'10')),0) Y10,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'11')>0)) Y11RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'11')),0) Y11,
MAX((SELECT COUNT(*) FROM T_GZFFMX WHERE FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'12')>0)) Y12RS,
NVL(SUM(FU_FENCH_GZTZQJE(A.YGID,'¼¼Äܹ¤×Ê',TO_CHAR(SYSDATE,'YYYY'),'12')),0) Y12
FROM T_GZFFMX A GROUP BY A.JGBH)