在mysql中使用case求和行的值

mkshixfv  于 2021-06-19  发布在  Mysql
关注(0)|答案(0)|浏览(447)

我正在做一个web项目,其中包括从mysql获取一些计算值。我已经编写了一个sql查询,它完成了这项工作,但是它是一个子查询,它大大降低了应用程序的速度。现在我正在尝试使用我在网上读到的连接优化查询。我是新的编程概念,特别是mysql,我想有人给我指出正确的道路。
当我尝试在mysql中使用sum和case获取值之和时,我得到了一个错误的值(超过值)。
以下是查询:

SELECT 
master.lgName,
master.wardName,
master.pUnitName,
SUM(voter_reg_no) AS vregno,
pvc_collected,
COUNT(DISTINCT master.wardName) as no_ward,
COUNT(DISTINCT CASE WHEN master.reportedpu = '1' THEN master.reportedpu END) as reportedpu,
SUM(CASE WHEN total != '' AND master.lgName = accreditation.lga_name THEN total ELSE 0 END) as acr,
SUM(CASE WHEN category = 'ivc'  THEN cno ELSE 0 END) AS ivc,
SUM(CASE WHEN category = 'tvc'  THEN cno ELSE 0 END) AS tvc,
SUM(CASE WHEN category = 'other'  THEN cno ELSE 0 END) AS oth,
SUM(CASE WHEN category = 'apc'  THEN cno ELSE 0 END) AS apc,
SUM(CASE WHEN category = 'pdp' THEN cno ELSE 0 END) AS pdp,
SUM(CASE WHEN category = 'adp' THEN cno ELSE 0 END) AS adp,
SUM(CASE WHEN category = 'adc'  THEN cno ELSE 0 END) AS adc,
SUM(CASE WHEN category = 'ad'  THEN cno ELSE 0 END) AS ad,
SUM(CASE WHEN category = 'sdp'  THEN cno ELSE 0 END) AS sdp
FROM master LEFT JOIN res ON master.lgName = res.lgName 
LEFT JOIN accreditation ON accreditation.lga_name = master.lgName
group by master.lgName ORDER by master.userCode ASC

下面是我以前的查询,它为我提供了正确的值,但减慢了应用程序的速度:

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, COUNT(DISTINCT wardName) as No_wards, 
count(pUnitName) as pu, 
            (select count(reportedpu) from master where wardName = a.wardName and reportedpu = 1) as reportedpu,
            (SELECT sum(total) FROM accreditation WHERE ward_name IN (select wardName from master where wardName = a.wardName)) AS acr, 
            (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'tvc') AS tvc,
    (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'apc') AS apc,
    (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'ivc') AS ivc,
    (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'pdp') AS pdp,
    (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'ad') AS ad,
    (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'adc') AS adc,
     (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'sdp') AS sdp,
      (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'adp') AS adp,

    (SELECT sum(cno) FROM res WHERE wardName IN (select wardName from master where wardName = a.wardName) and category = 'other') AS oth
    FROM master a group by a.lgName order by a.userCode asc

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题