各表主键:
表M_PSNMONTHACCOUNTRECORD的主键;表M_SJ_M_PERSON主键STATMONTH, PSNSN;表M_SJ_M_CORPORATION主键STATMONTH, CORPSN。
SQL语句:
SELECT *
FROM (SELECT 16 AS AGE,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND B.AGE <= 16
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
GROUP BY A.FEEMONTH, B.FEEAREA, C.CORPTYPE
UNION
SELECT B.AGE,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
GROUP BY B.AGE, A.FEEMONTH, B.FEEAREA, C.CORPTYPE
HAVING B.AGE BETWEEN 17 AND 64
UNION
SELECT 65 AS AGE,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
AND B.AGE >= 65
GROUP BY B.FEEAREA, A.FEEMONTH, B.FEEAREA, C.CORPTYPE)
表M_PSNMONTHACCOUNTRECORD的主键;表M_SJ_M_PERSON主键STATMONTH, PSNSN;表M_SJ_M_CORPORATION主键STATMONTH, CORPSN。
SQL语句:
SELECT *
FROM (SELECT 16 AS AGE,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND B.AGE <= 16
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
GROUP BY A.FEEMONTH, B.FEEAREA, C.CORPTYPE
UNION
SELECT B.AGE,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
GROUP BY B.AGE, A.FEEMONTH, B.FEEAREA, C.CORPTYPE
HAVING B.AGE BETWEEN 17 AND 64
UNION
SELECT 65 AS AGE,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
AND B.AGE >= 65
GROUP BY B.FEEAREA, A.FEEMONTH, B.FEEAREA, C.CORPTYPE)
2、GROUP尽量少用
SELECT case when A.AGE <= 16 then 16
when A.AGE >= 65 then 65
else A.AGE end age,
A.FEEMONTH,
B.FEEAREA,
C.CORPTYPE,
COUNT(B.PSNSN),
SUM(A.FEEBASE)
FROM BJMEDICARE_SJ.M_PSNMONTHACCOUNTRECORD A,
M_SJ_M_PERSON B,
M_SJ_M_CORPORATION C
WHERE A.PSNSN = B.PSNSN
AND B.STATMONTH = C.STATMONTH
AND B.CORPSN = C.CORPSN
AND A.FEEPSNTYPE NOT IN (12, 13, 14, 17)
GROUP BY case when A.AGE <= 16 then 16
when A.AGE >= 65 then 65,
else A.AGE end,A.FEEMONTH, B.FEEAREA, C.CORPTYPE
2, not in 不走索引,可以试着改用 not exists