declare @s varchar(8000)
set @s = ''select
@s = @s + ','+KhZt+'=sum(case KhZtID when '''+ID+''' then 1 end)'
from
KhZt
order by
IDset @s = 'select 业务员=case when YwR is null then ''合计'' else YwR end'+@s+',合计=count(*) from KhZy group by YwR with rollup'exec(@s)
set @s = ''select
@s = @s + ','+KhZt+'=sum(case KhZtID when '''+ID+''' then 1 end)'
from
KhZt
order by
IDset @s = 'select 业务员=case when YwR is null then ''合计'' else YwR end'+@s+',合计=count(*) from KhZy group by YwR with rollup'exec(@s)
create table #KhZy(ID varchar(10),KhMc varchar(10),KhZtID varchar(10),YwR varchar(10))
insert into #KhZy select '0001','客户1','02','李三'
insert into #KhZy select '0002','客户2','03','王五'
insert into #KhZy select '0003','客户3','01','李三'
insert into #KhZy select '0004','客户4','02','王五'
insert into #KhZy select '0005','客户5','03','王五'
insert into #KhZy select '0006','客户6','01','李三'
create table #KhZt(ID varchar(10),KhZt varchar(10))
insert into #KhZt select '01','状态1'
insert into #KhZt select '02','状态2'
insert into #KhZt select '03','状态3'
--执行动态交叉表查询
declare @s varchar(8000)
set @s = ''select @s = @s + ','+KhZt+'=sum(case KhZtID when '''+ID+''' then 1 end)'
from #KhZt order by IDset @s = 'select 业务员=case when YwR is null then ''合计'' else YwR end'+@s+',合计=count(*) from #KhZy group by YwR with rollup'
exec(@s)--输出查询结果
/*
业务员 状态1 状态2 状态3 合计
------ ----- ----- ----- -----
李三 2 1 NULL 3
王五 NULL 1 2 3
合计 2 2 2 6
*/--删除测试数据
drop table #KhZt,#KhZy
declare @str_sql varchar(8000)
set @str_sql=''
select @str_sql=@str_sql+',sum(case KhZtID when '''+[ID]+''' then 1 else 0 end) as '+KhZt+''
from KhZt
set @str_sql='select isnull(YwR,''合计'') as 业务员'+@str_sql+'
,count(*) as 合计
from KhZy
group by YwR
with rollup'
exec(@str_sql)
--结果
/*
业务员 状态1 状态2 状态3 合计
---------- ----------- ----------- ----------- -----------
李三 2 1 0 3
王五 0 1 2 3
合计 2 2 2 6
*/
(
ID varchar(20),
KhMc varchar(20),
KhZtID varchar(20),
Ywr varchar(20)
)
[create] table khZT
(
ID varchar(20),
KhZt varchar(20),
BZ varchar(20)
)
insert KhZy
select '0001','客户1','02','李三' [union]
select '0002','客户2','03','王五' [union]
select '0003','客户3','01','李三' [union]
select '0004','客户4','02','王五' [union]
select '0005','客户5','03','王五' [union]
select '0006','客户6','01','李三'
insert KhZT
select '01','状态1','' [union]
select '02','状态2','' [union]
select '03','状态3',''select * from khzy a,khzt b where a.khztid=b.id
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+khzt+']=sum(case when Khzt='''+khzt+''' then 1 end)'
from
(select a.KhztID,b.khzt from khzy a,khzt b where a.khztid=b.id) a
group by khzt
order by khzt
exec('select case when YwR is null then ''合计'' else YwR end as ''业务员'''+@sql+',合计=count(*) from (select a.Ywr,a.KhztID,b.khzt from khzy a,khzt b where a.khztid=b.id) a group by a.Ywr WITH ROLLUP')drop table khzy
drop table khzt业务员 状态1 状态2 状态3 合计
-------------------- ----------- ----------- ----------- -----------
李三 2 1 NULL 3
王五 NULL 1 2 3
合计 2 2 2 6
表1:KhZy(客户资源)
ID KhMc ....... KhZtID(客户状态ID) YwR(业务员) 时间
---------------------------------------------------------
0001 客户1 02 李三 2005-01-11
0002 客户2 03 王五 2005-01-11
0003 客户3 01 李三 2005-03-22
0004 客户4 02 王五 2005-01-11
0005 客户5 03 王五 2005-01-22
0006 客户6 01 李三 2005-02-11我要将时间为2005年1月的统计情况显示出来,应该如何写
create table #KhZy(ID varchar(10),KhMc varchar(10),KhZtID varchar(10),YwR varchar(10),Sj datetime)
insert into #KhZy select '0001','客户1','02','李三','2005-01-11'
insert into #KhZy select '0002','客户2','03','王五','2005-01-11'
insert into #KhZy select '0003','客户3','01','李三','2005-03-22'
insert into #KhZy select '0004','客户4','02','王五','2005-01-11'
insert into #KhZy select '0005','客户5','03','王五','2005-01-22'
insert into #KhZy select '0006','客户6','01','李三','2005-02-11'
create table #KhZt(ID varchar(10),KhZt varchar(10))
insert into #KhZt select '01','状态1'
insert into #KhZt select '02','状态2'
insert into #KhZt select '03','状态3'--执行动态交叉表查询
declare @s varchar(8000),@date varchar(10)set @s = ''
set @date = '2005-01-01'select @s = @s + ','+KhZt+'=sum(case KhZtID when '''+ID+''' then 1 end)'
from #KhZt order by IDset @s = 'select 业务员=case when YwR is null then ''合计'' else YwR end'
+@s
+',合计=count(*) from #KhZy where datediff(mm,sj,'''+@date+''')=0 group by YwR with rollup'
exec(@s)--输出查询结果
/*
业务员 状态1 状态2 状态3 合计
------ ----- ----- ----- -----
李三 NULL 1 NULL 1
王五 NULL 1 2 3
合计 NULL 2 2 4
*/--删除测试数据
drop table #KhZt,#KhZy
set @date='2005-01'
declare @str_sql varchar(8000)
set @str_sql=''
select @str_sql=@str_sql+',sum(case KhZtID when '''+[ID]+''' then 1 else 0 end) as '+KhZt+''
from KhZt
set @str_sql='select (case when grouping(YwR)=1 then ''合计'' else YwR end) as 业务员'+@str_sql+'
,count(*) as 合计
from KhZy
where convert(char(7),sj,120)='''+@date+'''
group by YwR
with rollup'
exec(@str_sql)
----------------------------------------------------------------------------------
--生成测试数据
create table #KhZy(ID varchar(10),KhMc varchar(10),KhZtID varchar(10),YwR varchar(10),Sj datetime)
insert into #KhZy select '0001','客户1','02','李三','2005-01-11'
insert into #KhZy select '0002','客户2','03','王五','2005-01-11'
insert into #KhZy select '0003','客户3','01','李三','2005-03-22'
insert into #KhZy select '0004','客户4','02','王五','2005-01-11'
insert into #KhZy select '0005','客户5','03','王五','2005-01-22'
insert into #KhZy select '0006','客户6','01','李三','2005-02-11'
create table #KhZt(ID varchar(10),KhZt varchar(10))
insert into #KhZt select '01','状态1'
insert into #KhZt select '02','状态2'
insert into #KhZt select '03','状态3'--执行动态交叉表查询
declare @s varchar(8000),@date varchar(10)set @s = ''
set @date = '2005-01-01'select @s = @s + ','+KhZt+'=sum(case KhZtID when '''+ID+''' then 1 end)'
from #KhZt order by IDset @s = 'select 业务员=case when YwR is null then ''合计'' else YwR end'
+@s
+',合计=count(*) into ##t from #KhZy where datediff(mm,sj,'''+@date+''')=0 group by YwR with rollup'
exec(@s)select * from ##t--输出查询结果
/*
业务员 状态1 状态2 状态3 合计
------ ----- ----- ----- -----
李三 NULL 1 NULL 1
王五 NULL 1 2 3
合计 NULL 2 2 4
*/--删除测试数据
drop table #KhZt,#KhZy
你們上班是不是沒有什麼事啊,呵呵,回答問題真是及時,羨慕這樣的工作!