如何优化，提供查询统计效率？
SELECT t1.TownCode,t1.TownName,
WcrkAll=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode),
WcrkMan=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1),
WcrkWoman=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2),
A015=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Age BETWEEN 0 AND 15),
A015Man=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 0 AND 15),
A015Woman=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 0 AND 15),
A1618=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Age BETWEEN 16 AND 18),
A1618ManGz=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 16 AND 18 AND IsWorkCode<200),
A1618WomanGz=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 16 AND 18 AND IsWorkCode<200),
A1618ManJw=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 16 AND 18 AND IsWorkCode=203),
A1618WomanJw=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 16 AND 18 AND IsWorkCode=203),
A1618ManJx=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 16 AND 18 AND IsWorkCode=202),
A1618WomanJx=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 16 AND 18 AND IsWorkCode=202),
A1618ManStbh=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 16 AND 18 AND IsWorkCode=201),
A1618WomanStbh=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 16 AND 18 AND IsWorkCode=201),
A1925=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Age BETWEEN 19 AND 25),
A1925ManGz=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 19 AND 25 AND IsWorkCode<200),
A1925WomanGz=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 19 AND 25 AND IsWorkCode<200),
A1925ManJw=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 19 AND 25 AND IsWorkCode=203),
A1925WomanJw=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 19 AND 25 AND IsWorkCode=203),
A1925ManJx=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 19 AND 25 AND IsWorkCode=202),
A1925WomanJx=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 19 AND 25 AND IsWorkCode=202),
A1925ManStbh=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=1 AND Age BETWEEN 19 AND 25 AND IsWorkCode=201),
A1925WomanStbh=(SELECT COUNT(1) FROM vJJDC_QuestionnaireList WHERE Town=t1.TownCode AND Sex=2 AND Age BETWEEN 19 AND 25 AND IsWorkCode=201)
FROM CODE_Town t1 ORDER BY t1.TownCode ASC
------解决思路----------------------
像 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次，所以优化效果是很可观且明显的。