我是一个会计出身的编程新手。我写了这个查询,它可以工作,但非常缓慢。我想知道是否有一种方法可以优化它的速度。
我要从以下表中获取数据:
主表
事件表
结果表
认证表
以下是查询:
SELECT a.lgName AS lgname
, a.wardName AS wardName
, a.pUnitName AS pUnitName
, SUM(a.pvc_collected) AS pvc
, SUM(a.voter_reg_no) AS purvs
, ( SELECT COUNT(pUnitName)
FROM master
WHERE wardName = a.wardName) AS No_Poll_Unitss
, ( SELECT COUNT(reportedpu)
FROM master
WHERE wardName = a.wardName
AND reportedpu = 1) AS reportedpu
, ( SELECT COUNT(pUnitName)
FROM master
WHERE pUnitName = a.pUnitName) AS No_Poll_Unitss
, ( SELECT COUNT(reportedpu)
FROM master
WHERE pUnitName = a.pUnitName
AND reportedpu = 1) AS reportedpu
, ( SELECT SUM(total)
FROM accreditation
WHERE pu_name IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)) AS acr
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'tvc') AS tvc
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'apc') AS apc
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'ivc') AS ivc
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'pdp') AS pdp
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'lp') AS lp
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'adc') AS adc
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'sdp') AS sdp
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'adp') AS adp
, ( SELECT SUM(cno)
FROM res
WHERE pUnitName IN ( SELECT pUnitName
FROM master
WHERE pUnitName = a.pUnitName)
AND category = 'other') AS oth
FROM master AS a
GROUP BY a.pUnitName, a.userCode
ORDER BY a.userCode ASC;
EXPLAIN
语句结果! - Madhur Bhaiya