SQL语句group by 的求和sum

 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