Question

MySql存储过程根据基础表向统计表导值

简单说就是
客服通话表A，有坐席ID(agentid)和服务评价(content)2个字段
客服通话评价统计表B，有坐席ID(agentid)和好评(good)、中评(normal)、差评(bad)4个字段
就像10086人工服务后要评价满意度一样，
表A存了所有客服当天的每一条记录(每通电话都有一条记录)；
表B统计每个客服一天下来好、中、差评的数量
现在要，根据表A的content存的值是1、2、3，分别代表好、中、差。将表A的数据统计到表B中，存储过程如何写？

Answer 1

select agentid,sum(good) as good,sum(normal) as normal,sum(bad) as bad
from
(select agentid,1 as good,0 as normal,0 as bad
from A where content=1
union all
select agentid,0 as good,1 as normal,0 as bad
from A where content=2
union all
select agentid,0 as good,0 as normal,1 as bad
from A where content=3
)B
group by agentid

追问

首先感谢你的回答，但是我需要按天统计然后插入B表，B表中除了好、中、差之外还有个字段评价数(用来统计每个客服当天评价的数量)，然后还有个统计日期，如何做。。。先提高20分悬赏，解决后追加100~

追答

select agentid,date,SUM(count)as count,sum(good) as good,sum(normal) as normal,sum(bad) as bad
from
(select agentid,date,1 as count,1 as good,0 as normal,0 as bad
from A where content=1
union all
select agentid,date,1 as count,0 as good,1 as normal,0 as bad
from A where content=2
union all
select agentid,date,1 as count,0 as good,0 as normal,1 as bad
from A where content=3
)B
group by agentid,date
这样？？

追问

不是，A表中记录日期的字段是recordtime,B表中记录日期的字段是reportdate。要按天将A表中的数据放到B表中。也就是说B表中的每一条数据都是一天内某个坐席客服的评价统计

不是，A表中记录日期的字段是recordtime,B表中记录日期的字段是reportdate。要按天将A表中的数据放到B表中。也就是说B表中的每一条数据都是一天内某个坐席客服的评价统计

追答

一个datetime，一个date？
转换下就可以了么

追问

不是，这2个表的日期都是varchar类型的字符串

追答

我不明白还有什么问题

追问

我要将一个固定日期(比如recordtime='20140506')的数据放到B表中，你上面出来的结果是个集合，如何按照条件放到B表中。。。我存储过程很菜，能完整点不= =？

追答

--创建存储
create proc sp_test
@date varchar(10)
as

insert into B (agentid,reportdate,count,good,normal,bad)
select agentid,recordtime,SUM(count)as count,sum(good) as good,sum(normal) as normal,sum(bad) as bad
from
(select agentid,recordtime,1 as count,1 as good,0 as normal,0 as bad
from A where content=1 and recordtime=@date
union all
select agentid,recordtime,1 as count,0 as good,1 as normal,0 as bad
from A where content=2 and recordtime=@date
union all
select agentid,recordtime,1 as count,0 as good,0 as normal,1 as bad
from A where content=3 and recordtime=@date
)B
group by agentid,recordtime
--按日期执行
exec sp_test '20140506'
这样？

追问

差不多了，还差最后一点，就是B表在实现上面功能的同时还要从C表通过agentid获取坐席客服的名称name，怎么做？

差不多了，还差最后一点，就是B表在实现上面功能的同时还要从C表通过agentid获取坐席客服的名称name，怎么做？

追答

--创建存储
create proc sp_test
@date varchar(10)
as

insert into B (agentid,reportdate,name,count,good,normal,bad)
select B.agentid,recordtime,C.name,SUM(count)as count,sum(good) as good,sum(normal) as normal,sum(bad) as bad
from
(select agentid,recordtime,1 as count,1 as good,0 as normal,0 as bad
from A where content=1 and recordtime=@date
union all
select agentid,recordtime,1 as count,0 as good,1 as normal,0 as bad
from A where content=2 and recordtime=@date
union all
select agentid,recordtime,1 as count,0 as good,0 as normal,1 as bad
from A where content=3 and recordtime=@date
)B
left join C on C.agentid=B.agentid
group by B.agentid,recordtime,name
--按日期执行
exec sp_test '20140506'