有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的查询。
结果类似: