SQL计算出百分比

有clients和lead_sources俩表。mysql数据库。

lead_sources表结构类似：

clients表中的lead_source_id是外键。现在要统计某时间段内client内每种lead_source所占百分比

select 
a.L_name, 
ROUND((a.L_sub_count*1.0/b.total_count*100),1) as perTotal,
a.L_sub_count,
b.total_count
from
(
SELECT LeadSource.name as L_name, count(*) as L_sub_count
FROM clients as Client,lead_sources as LeadSource
where 
    Client.lead_source_id = LeadSource.id 
    and LENGTH(Client.lead_source_id) > 0
    and Client.created_date BETWEEN '2012-09-01' AND '2012-11-01'
GROUP BY LeadSource.`name`
order by L_sub_count desc
)  a,
(
select count(*) as Total_count FROM clients as Client,lead_sources as LeadSource where     
    Client.lead_source_id = LeadSource.id 
    and LENGTH(Client.lead_source_id) > 0
    and Client.created_date BETWEEN '2012-09-01' AND '2012-11-01'
) b;

这个SQL有三个select，所以看成三部分，我的理解，后两个是构造虚表和字段。目的就是用于第一个select的查询。

结果类似：

转载于:https://www.cnblogs.com/mafeifan/p/3488156.html