数据库中的表如下:
需要查询每个人去每个客户的天数,每个客户去的总天数。
SQL语句如下:
1、先计算每个客户的总天数
select distinct
'' as '姓名',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='北路科技') as '北路科技',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='山西煤矿' ) as '山西煤矿',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='陕西煤矿' ) '陕西煤矿'
from SMS_DateReport
2、计算每个人去每个客户的次数
SELECT
vc_EmployeeName,
count( distinct CASE vc_CustomerName WHEN '北路科技' THEN dt_ReceiveTime else null end) , --as '北路科技',
count( distinct CASE vc_CustomerName WHEN '山西煤矿' THEN dt_ReceiveTime else null end), -- as '山西煤矿',
count( distinct CASE vc_CustomerName WHEN '陕西煤矿' THEN dt_ReceiveTime else null end) -- as '陕西煤矿'
FROM SMS_DateReport
group by vc_EmployeeName
3、合并行
select distinct
'' as '姓名',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='北路科技') as '北路科技',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='山西煤矿' ) as '山西煤矿',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='陕西煤矿' ) '陕西煤矿'
from SMS_DateReport
union all
SELECT
vc_EmployeeName,
count( distinct CASE vc_CustomerName WHEN '北路科技' THEN dt_ReceiveTime else null end) , --as '北路科技',
count( distinct CASE vc_CustomerName WHEN '山西煤矿' THEN dt_ReceiveTime else null end), -- as '山西煤矿',
count( distinct CASE vc_CustomerName WHEN '陕西煤矿' THEN dt_ReceiveTime else null end) -- as '陕西煤矿'
FROM SMS_DateReport
group by vc_EmployeeName
最终结果如下:
4、对于客户很多的情况,可以使用游标,生成SQL字符串,再查询。