像 vJJDC_QuestionnaireList 应该是个视图 需要多次扫描的尽量只扫描一次,比如 Sum(Case When .. Then 1 Else 0 End) as C1,Sum(Case When ... Then 1 Else 0 End) as C2 一次性统计出
try: SELECT t1.TownCode,t1.TownName, sum(*) as WcrkAll, sum(case when t2.Sex=1 then 1 else 0 end) as WcrkMan, sum(case when t2.Sex=2 then 1 else 0 end) as WcrkWoman, sum(case when t2.Age BETWEEN 0 AND 15 then 1 else 0 end ) as A015, sum(case when t2.Sex=1 AND t2.Age BETWEEN 0 AND 15 then 1 else 0 end) as A015Man, sum(case when t2.Sex=2 AND t2.Age BETWEEN 0 AND 15 then 1 else 0 end) as A015Woman, sum(case when t2.Age BETWEEN 16 AND 18 then 1 else 0 end) as A1618, ...... FROM CODE_Town t1 inner join vJJDC_QuestionnaireList t2 on t1.TownCode=t2.Town group by t1.TownCode,t1.TownName ORDER BY t1.TownCode ASC
用case when就可以把这么多的子查询去掉 ,改成类似这样的: SELECT t1.TownCode,t1.TownName, WcrkAll=count(1), WcrkMan=count(case when Sex=1 then 1 else null end), WcrkWoman=count(case when Sex=2 then 1 else null end), A015=count(case when Age BETWEEN 0 AND 15 then 1 else null end), A015Man=count(case when Sex=1 AND Age BETWEEN 0 AND 15 then 1 else null end),...
FROM CODE_Town t1 left join vJJDC_QuestionnaireList t2 on t2.Town=t1.TownCode ORDER BY t1.TownCode ASC
SELECT t1.TownCode,MAX(t1.TownName), SUM(CASE WHEN 你的WHERE条件1=True THEN 1 ELSE 0 END), SUM((CASE WHEN 你的WHERE条件2=True THEN 1 ELSE 0 END)), SUM((CASE WHEN 你的WHERE条件N=True THEN 1 ELSE 0 END)) FROM vJJDC_QuestionnaireList t1 GROUP BY t1.TownCode上面的语句就行了,这条语句只对表扫描了一次,你的语句对表扫描了N次,所以优化效果是很可观且明显的。
需要多次扫描的尽量只扫描一次,比如 Sum(Case When .. Then 1 Else 0 End) as C1,Sum(Case When ... Then 1 Else 0 End) as C2 一次性统计出
SELECT t1.TownCode,t1.TownName,
sum(*) as WcrkAll,
sum(case when t2.Sex=1 then 1 else 0 end) as WcrkMan,
sum(case when t2.Sex=2 then 1 else 0 end) as WcrkWoman,
sum(case when t2.Age BETWEEN 0 AND 15 then 1 else 0 end ) as A015,
sum(case when t2.Sex=1 AND t2.Age BETWEEN 0 AND 15 then 1 else 0 end) as A015Man,
sum(case when t2.Sex=2 AND t2.Age BETWEEN 0 AND 15 then 1 else 0 end) as A015Woman,
sum(case when t2.Age BETWEEN 16 AND 18 then 1 else 0 end) as A1618,
...... FROM CODE_Town t1 inner join vJJDC_QuestionnaireList t2 on t1.TownCode=t2.Town
group by t1.TownCode,t1.TownName
ORDER BY t1.TownCode ASC
WcrkAll=count(1),
WcrkMan=count(case when Sex=1 then 1 else null end),
WcrkWoman=count(case when Sex=2 then 1 else null end),
A015=count(case when Age BETWEEN 0 AND 15 then 1 else null end),
A015Man=count(case when Sex=1 AND Age BETWEEN 0 AND 15 then 1 else null end),...
FROM CODE_Town t1
left join vJJDC_QuestionnaireList t2
on t2.Town=t1.TownCode
ORDER BY t1.TownCode ASC
SELECT t1.TownCode,MAX(t1.TownName),
SUM(CASE WHEN 你的WHERE条件1=True THEN 1 ELSE 0 END),
SUM((CASE WHEN 你的WHERE条件2=True THEN 1 ELSE 0 END)),
SUM((CASE WHEN 你的WHERE条件N=True THEN 1 ELSE 0 END))
FROM vJJDC_QuestionnaireList t1
GROUP BY t1.TownCode上面的语句就行了,这条语句只对表扫描了一次,你的语句对表扫描了N次,所以优化效果是很可观且明显的。