22,207
社区成员
发帖
与我相关
我的任务
分享
create table tb(客服名 varchar(10),评分 int)
insert into tb select '李四',3
insert into tb select '张三',3
insert into tb select '李四',2
insert into tb select '老五',1
insert into tb select '张三',3
insert into tb select '老五',3
insert into tb select '张三',1
go
select a.*,b.总分 from
(select * from tb pivot(count(评分) for 评分 in ([3],[2],[1])) as p)a
join
(select 客服名,sum(评分) as 总分 from tb group by 客服名) b
on a.客服名=b.客服名
go
/*
客服名 3 2 1 总分
---------- ----------- ----------- ----------- -----------
老五 1 0 1 4
李四 1 1 0 5
张三 2 0 1 7
(3 行受影响)
*/
go
drop table tb
select 客服名,
[1分]=sum(case when 评分=N'1' then 1 else '' end),
[2分]=sum(case when 评分=N'2' then 1 else '' end),
[3分]=sum(case when 评分=N'3' then 1 else '' end),
sum(评分) as 总分
from 表名
group by 客服名
order by 总分 desc
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([客服名] nvarchar(2),[评分] int)
Insert #T
select N'李四',3 union all
select N'张三',3 union all
select N'李四',2 union all
select N'老五',1 union all
select N'张三',3 union all
select N'老五',3 union all
select N'张三',1
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+N','+quotename(RTRIM(评分)+N'分')+N'=sum(case when 评分=N'+quotename(评分,'''')+N' then 1 else '''' end)'
from #T group by 评分
--顯示生成語句
print N'select 客服名'+@s+N',sum(评分) as 总分 from #T group by 客服名 order by 总分 desc'
exec(N'select 客服名'+@s+N',sum(评分) as 总分 from #T group by 客服名 order by 总分 desc')
go
/*
客服名 1分 2分 3分 总分
张三 1 0 2 7
李四 0 1 1 5
老五 1 0 1 4
*/
<%Sql= "select top 10 ddkf,sum(pjpf) as pjpf from dingdan group by ddkf order by sum(pjpf) desc "
Rs2.Open Sql,Conn,1,1
ddkff=rs2("ddkf")
s=0
Do While Not Rs2.Eof
s=s+1 %>
<tr>
<td bgcolor="#FFFFFF"><div align="center">第<%=s%>名</div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("ddkf")%></div></td>
<td bgcolor="#FFFFFF"><div align="center">这里放3分的数量</div></td>
<td bgcolor="#FFFFFF"><div align="center">这里放2分的数量/div></td>
<td bgcolor="#FFFFFF"><div align="center">这里放1分的数量</div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("pjpf")%></div></td>
</tr>
<%
ss=ss+1
if ss>=10 then exit do
Rs2.movenext
Loop
rs2.close
%>
<%Sql= "select top 10 ddkf,3分=sum(case when pjpf=3 then 1 else 0 end),2分=sum(case when pjpf=2 then 1 else 0 end),1分=sum(case when pjpf=1 then 1 else 0 end) from ddkf group by ddkf order by sum(pjpf) desc "
Rs2.Open Sql,Conn,1,1
s=0
Do While Not Rs2.Eof
s=s+1 %>
<tr>
<td bgcolor="#FFFFFF"><div align="center">第<%=s%>名</div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("ddkf")%></div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("3分")%></div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("2分")%></div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("1分")%></div></td>
<td bgcolor="#FFFFFF"><div align="center"><%=rs2("pjpf")%></div></td>
</tr>
<%
ss=ss+1
if ss>=10 then exit do
Rs2.movenext
Loop
rs2.close
%>
create table tb(客服名 varchar(10),评分 int)
insert into tb select '李四',3
insert into tb select '张三',3
insert into tb select '李四',2
insert into tb select '老五',1
insert into tb select '张三',3
insert into tb select '老五',3
insert into tb select '张三',1
go
declare @sql varchar(max)
set @sql = 'select 客服名'
select @sql = @sql + ',sum(case when 评分 = '+ltrim(评分)+' then 1 else 0 end) ['+ltrim(评分)+'分]'
from tb
group by 评分
select @sql = @sql + ',sum(评分) as 总分 from tb group by 客服名'
exec(@sql)
go
drop table tb
/*************
客服名 1分 2分 3分 总分
---------- ----------- ----------- ----------- -----------
老五 1 0 1 4
李四 0 1 1 5
张三 1 0 2 7
(3 行受影响)
create table tb(客服名 varchar(10),评分 int)
insert into tb select '李四',3
insert into tb select '张三',3
insert into tb select '李四',2
insert into tb select '老五',1
insert into tb select '张三',3
insert into tb select '老五',3
insert into tb select '张三',1
go
declare @sql varchar(max)
set @sql = 'select 客服名'
select @sql = @sql + ',sum(case when 评分 = '+ltrim(评分)+' then 1 else 0 end) ['+ltrim(评分)+'分]'
from tb
group by 评分
select @sql = @sql + ' from tb group by 客服名'
exec(@sql)
/*
客服名 3分 2分 1分 总分
---------- ----------- ----------- ----------- -----------
张三 2 0 1 7
李四 1 1 0 5
老五 1 0 1 4
(3 行受影响)
*/
go
drop table tb
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (客服名 nvarchar(4),评分 int)
insert into [TB]
select '李四',3 union all
select '张三',3 union all
select '李四',2 union all
select '老五',1 union all
select '张三',3 union all
select '老五',3 union all
select '张三',1
select * from [TB]
select top 10 客服名,
评分3=sum(case when 评分=3 then 1 else 0 end),
评分2=sum(case when 评分=2 then 1 else 0 end),
评分1=sum(case when 评分=1 then 1 else 0 end)
from TB
group by 客服名
order by sum(评分) desc
/*
客服名 评分3 评分2 评分1
---- ----------- ----------- -----------
张三 2 0 1
李四 1 1 0
老五 1 0 1
(3 行受影响)
*/
create table tb(客服名 varchar(10),评分 int)
insert into tb select '李四',3
insert into tb select '张三',3
insert into tb select '李四',2
insert into tb select '老五',1
insert into tb select '张三',3
insert into tb select '老五',3
insert into tb select '张三',1
go
select 客服名,
sum(case when 评分=3 then 1 else 0 end)[3分],
sum(case when 评分=2 then 1 else 0 end)[2分],
sum(case when 评分=1 then 1 else 0 end)[1分],
SUM(评分)总分
from tb
group by 客服名
order by 5 desc
/*
客服名 3分 2分 1分 总分
---------- ----------- ----------- ----------- -----------
张三 2 0 1 7
李四 1 1 0 5
老五 1 0 1 4
(3 行受影响)
*/
go
drop table tb
select top 10 客服名,评分3=sum(case when 评分=3 then 1 else 0 end),
评分2=sum(case when 评分=2 then 1 else 0 end),
评分1=sum(case when 评分=1 then 1 else 0 end)
from 客服名 group by 客服名 order by sum(评分) desc
select top 10 客服名,评分3=sum(case when 评分=3 then 1 else 0 end),
评分2=sum(case when 评分=2 then 1 else 0 end),
评分1=sum(case when 评分=1 then 1 else 0 end)
from 客服名 group by 客服名 order by sum(评分)