SQL 报表求助
表结构和需要的结果如下图，求高人指点
[解决办法]
1. 需要的结果DPD15,DPD16,DPD17是 固定死的列?
是不是可以理解 为DiaClient.DPD 字段值 只有 15,16,17 ?
2. DiaTask,DiaBatch表好像不会用到哟!
另:希望能给出 表结构的 创建及初始化语句脚本。
[解决办法]
我将表简化了下,只写出了total的
create table #A(taskid varchar(2),clientid varchar(2),agent varchar(20))
insert into #A
select 'T1','C1','Agent01' union all select 'T1','C2','Agent02' union all
select 'T1','C3','Agent02' union all select 'T1','C4','Agent01'
create table #B(clientid varchar(10),dpd varchar(5))
insert into #B
select 'C1','15' union all select 'C2','16' union all
select 'C3','15' union all select 'C4','17'
create table #C (Clientid varchar(3),[datetime] varchar(20) )
insert into #C
select 'C1','2013-12-4' union all select 'C2','2013-12-4'
union all select 'C3','2013-12-4' union all select 'C4','2013-12-4'
declare @s varchar(max)
set @s=''
select @s=@s+','+Quotename('DPD'+dpd)+'=sum(case when dpd='+quotename(dpd,'''')+' then 1 else 0 end)' from #B group by dpd
set @s='select [datetime],agent'+@s+' from #A a,#B b,#C c where a.clientid=b.clientid and a.clientid=c.clientid group by [datetime],agent
union all
select ''Total'','''''+@s+' from #A a,#B b,#C c where a.clientid=b.clientid and a.clientid=c.clientid'
exec(@s)
[解决办法]
create table DiaTaskClient
(taskid varchar(10),
clientid varchar(10),
agent varchar(10),
caseresult varchar(10))
create table DiaClient
(clientid varchar(10),
batchid varchar(10),
DPD int)
create table DiaTrial
(taskid varchar(10),
clientid varchar(10),
[datetime] varchar(15))
insert into DiaTaskClient
select 'T1','C1','agent01','成功扣款' union all
select 'T1','C2','agent02','成功扣款' union all
select 'T1','C3','agent02','成功扣款' union all
select 'T1','C4','agent01','成功扣款'
insert into DiaClient
select 'C1','B1',15 union all
select 'C2','B1',16 union all
select 'C3','B1',15 union all
select 'C4','B1',17
insert into DiaTrial
select 'T1','C1','2013/12/4' union all
select 'T1','C2','2013/12/4' union all
select 'T1','C3','2013/12/4' union all
select 'T1','C4','2013/12/4'
declare @tsql varchar(6000),@tsql2 varchar(6000),@tsql3 varchar(6000)
select @tsql=isnull(@tsql+',','')
+'rtrim(max(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ''DPD'+rtrim(DPD)+''' ',
@tsql2=isnull(@tsql2+',',''''',''Total'',')
+'rtrim(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ',
@tsql3=isnull(@tsql3+',',''''',''Avg'',')
+'rtrim(cast(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)/
(select count(distinct a.[datetime]+b.agent)*1.0
from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid) as decimal(5,1)))'
from (select distinct DPD from DiaClient) t
select @tsql2='select '+@tsql2+' from DiaClient',
@tsql3='select '+@tsql3+' from DiaClient'
select @tsql='select a.[datetime],b.agent,'+@tsql
+' from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid
inner join DiaClient c on b.clientid=c.clientid
group by a.[datetime],b.agent
union all '+@tsql2
+' union all '+@tsql3
exec(@tsql)
/*
datetime agent DPD15 DPD16 DPD17
--------------- ---------- ------------- ------------- -------------
2013/12/4 agent01 1 0 1
2013/12/4 agent02 1 1 0
Total 2 1 1
Avg 1.0 0.5 0.5
(4 row(s) affected)
*/
create table DiaTaskClient
(taskid varchar(10),
clientid varchar(10),
agent varchar(10),
caseresult varchar(10))
create table DiaClient
(clientid varchar(10),
batchid varchar(10),
DPD int)
create table DiaTrial
(taskid varchar(10),
clientid varchar(10),
[datetime] varchar(15))
insert into DiaTaskClient
select 'T1','C1','agent01','成功扣款' union all
select 'T1','C2','agent02','成功扣款' union all
select 'T1','C3','agent02','成功扣款' union all
select 'T1','C4','agent01','成功扣款'
insert into DiaClient
select 'C1','B1',15 union all
select 'C2','B1',16 union all
select 'C3','B1',15 union all
select 'C4','B1',17
insert into DiaTrial
select 'T1','C1','2013/12/4' union all
select 'T1','C2','2013/12/4' union all
select 'T1','C3','2013/12/4' union all
select 'T1','C4','2013/12/4'
declare @tsql varchar(6000),@tsql2 varchar(6000),@tsql3 varchar(6000)
select @tsql=isnull(@tsql+',','')
+'rtrim(max(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ''DPD'+rtrim(DPD)+''' ',
@tsql2=isnull(@tsql2+',',''''',''Total'',')
+'rtrim(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)) ',
@tsql3=isnull(@tsql3+',',''''',''Avg'',')
+'rtrim(cast(sum(case when DPD='+rtrim(DPD)+' then 1 else 0 end)/
(select count(distinct a.[datetime]+b.agent)*1.0
from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid) as decimal(5,1))) '
from (select number 'DPD'
from master.dbo.spt_values
where type='P' and number between 14 and 20) t
select @tsql2='select '+@tsql2+' from DiaClient',
@tsql3='select '+@tsql3+' from DiaClient'
select @tsql='select a.[datetime],b.agent,'+@tsql
+' from DiaTrial a
inner join DiaTaskClient b on a.taskid=b.taskid and a.clientid=b.clientid
inner join DiaClient c on b.clientid=c.clientid
group by a.[datetime],b.agent
union all '+@tsql2
+' union all '+@tsql3
exec(@tsql)
/*
datetime agent DPD14 DPD15 DPD16 DPD17 DPD18 DPD19 DPD20
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2013/12/4 agent01 0 1 0 1 0 0 0
2013/12/4 agent02 0 1 1 0 0 0 0
Total 0 2 1 1 0 0 0
Avg 0.0 1.0 0.5 0.5 0.0 0.0 0.0
(4 row(s) affected)
*/
