A表:社区表 17条,B表:住户表 17万多条,C表:分套表18万多条 三个表A表一对多B表,B表一对多C 表,现在要通过GROUP BY A表,统计B表内符合C表条件的不重复条数
我已经写好一个,运行结果正确,只是速度太慢,数据库已经加了索引不带任何条件查询时间:66.831秒,加上合计后查询时间:73.055秒SELECT A.COMM_CODE,
A.COMM_NAME,
(SELECT COUNT(DISTINCT(B.ZHBH))
FROM B, C
WHERE SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH) 合计,
(SELECT COUNT(DISTINCT(B.ZHBH))
FROM B, C
WHERE SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH
AND C.BUILD_SQUARE < 60 AND 分套条件 AND 住户条件) ,
(SELECT COUNT(DISTINCT(B.ZHBH))
FROM B, C
WHERE SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH
AND C.BUILD_SQUARE BETWEEN 60 AND 80 AND 分套条件 AND 住户条件)
FROM A
WHERE 社区表条件
GROUP BY A.COMM_CODE, A.COMM_NAME
我已经写好一个,运行结果正确,只是速度太慢,数据库已经加了索引不带任何条件查询时间:66.831秒,加上合计后查询时间:73.055秒SELECT A.COMM_CODE,
A.COMM_NAME,
(SELECT COUNT(DISTINCT(B.ZHBH))
FROM B, C
WHERE SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH) 合计,
(SELECT COUNT(DISTINCT(B.ZHBH))
FROM B, C
WHERE SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH
AND C.BUILD_SQUARE < 60 AND 分套条件 AND 住户条件) ,
(SELECT COUNT(DISTINCT(B.ZHBH))
FROM B, C
WHERE SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH
AND C.BUILD_SQUARE BETWEEN 60 AND 80 AND 分套条件 AND 住户条件)
FROM A
WHERE 社区表条件
GROUP BY A.COMM_CODE, A.COMM_NAME
SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE吗试试这个
select COMM_CODE, COMM_NAME, sum(f1) f1, sum(f2) f2, sum(f3) f3 from (SELECT A.COMM_CODE, A.COMM_NAME, COUNT(DISTINCT(B.ZHBH)) f1, 0 f2, 0 f3
FROM C, B,A
where C.HOUSEHOLDER_NO = B.ZHBH and SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE and 社区表条件
group by A.COMM_CODE, A.COMM_NAMEunion allSELECT A.COMM_CODE, A.COMM_NAME, 0 f1, COUNT(DISTINCT(HOUSEHOLDER_NO)) f2, 0 f3
FROM C, B, A
WHERE C.HOUSEHOLDER_NO = B.ZHBH and C.BUILD_SQUARE < 60 AND 分套条件 AND 住户条件 and SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE and 社区表条件
group by A.COMM_CODE, A.COMM_NAMEunion allSELECT A.COMM_CODE, A.COMM_NAME, 0 f1, 0 f2, COUNT(DISTINCT(HOUSEHOLDER_NO)) f3
FROM C, B, A
WHERE C.BUILD_SQUARE BETWEEN 60 AND 80 AND 分套条件 AND 住户条件 and SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE and 社区表条件
group by HOUSEHOLDER_NO,B.SQDM
)group by COMM_CODE, COMM_NAME;
sum(case when C.BUILD_SQUARE < 60 AND 分套条件 AND 住户条件 then zcount else 0 end) 合计2,
sum(case when C.BUILD_SQUARE BETWEEN 60 AND 80 AND 分套条件 AND 住户条件 then zcount else 0 end) 合计3
from (
select substr(sqdm, 1, 3) sqdm, zhbh, count(distinct(zhbh)) zcount
from B
group by zhbh
) b inner join a on b.sqdm = a.COMM_CODE
inner join c on b.zhbh = c.HOUSEHOLDER_NO
A.COMM_NAME,
COUNT(DISTINCT B.ZHBH) 合计,
COUNT(DISTINCT CASE WHEN C.BUILD_SQUARE < 60 AND 分套条件 AND 住户条件
THEN B.ZHBH ELSE NULL END) ,
COUNT(DISTINCT CASE WHEN C.BUILD_SQUARE BETWEEN 60 AND 80 AND 分套条件 AND 住户条件
THEN B.ZHBH ELSE NULL END)
FROM A ,B, C
WHERE 社区表条件 AND SUBSTR(B.SQDM, 1, 3) = A.COMM_CODE
AND C.HOUSEHOLDER_NO = B.ZHBH
GROUP BY A.COMM_CODE, A.COMM_NAME
索引呢SELECTA.COMM_CODE,
A.COMM_NAME,
按条件SUM出来
FROM
(SELECT B.ZHBH,C.BUILD_SQUARE,SUBSTR(B.SQDM,1,3) SQDM_S13
FROM B,C
WHERE B.ZHBH = C.HOUSEHODER_NO) BC,
A
WHERE A.COMM_CODE = BC.SQDM_S13
GROUP BY A.COMM_CODE, A.COMM_NAME