SQL语句的针对GROUP BY 分组求和
方法一:
SELECT factors_value,SUM(pv) AS sum_pv,SUM(pv)/
(SELECT SUM(sc.sum_pv) FROM (
SELECT factors_value,SUM(pv) AS sum_pv FROM `stats_come`
WHERE web_id='67679008' AND factors_id='20'
GROUP BY factors_value
ORDER BY sum_pv DESC
) sc LIMIT 1 ) AS sum_pv_rate
FROM `stats_come`
WHERE web_id='67679008' AND factors_id='20'
GROUP BY factors_value
ORDER BY sum_pv DESC
方法二:
SELECT sc.factors_value,ROUND(m.maxpv/m.total*100,1) AS ratio
FROM (SELECT factors_value,SUM(pv) AS pv FROM stats_come
WHERE factors_id='20' AND web_id='67679008' GROUP BY factors_value) sc
INNER JOIN (SELECT MAX(a.pv) AS maxpv,SUM(a.pv) AS total
FROM (SELECT SUM(pv) AS pv FROM stats_come WHERE factors_id='20' AND web_id='67679008'
GROUP BY factors_value ) a ) m ON sc.pv = m.maxpv
方法三:
SELECT round(b.pv/a.pv*100,1) AS pv_ratio FROM
(SELECT SUM(pv) AS pv FROM stats_come WHERE web_id=? AND factors_id='6') a LEFT JOIN
(SELECT SUM(pv) AS pv FROM stats_come WHERE web_id=? AND factors_id='6' AND LOCATE(factors_value,?,1)>0) b ON 1=1