关于日期名次统计交叉表!高分求解！（邹的书我已买但未看懂，希望指点！）

Question

create table abc(姓名 varchar(20),日期 datetime,销售金额 int)
go
insert abc values('张三','2005-11-01',100)
insert abc values('张三','2005-11-02',200)
insert abc values('李四','2005-11-03',150)
insert abc values('张三','2005-12-01',300)
insert abc values('李四','2005-12-02',400)
insert abc values('王五','2005-12-03',700)go
select 姓名,[11月份销售金额]=isnull(sum(case when month(日期)=11 then 销售金额 end),0),
[11月份次数]=isnull(sum(case when month(日期)=11 then 1 end),0),
[12月份销售金额]=isnull(sum(case when month(日期)=12 then 销售金额 end),0),
[12月份次数]=isnull(sum(case when month(日期)=12 then 1 end),0)
from abc group by 姓名drop table abc
--还不完善，明早继续。

Answer 1

create table t
(姓名 varchar(20),日期 datetime,销售金额 int)insert t values ('张三','2005-11-01',100)
insert t values ('张三','2005-11-02',200)
insert t values ('李四','2005-11-03',150)
insert t values ('张三','2005-12-01',300)
insert t values ('李四','2005-12-02',400)
insert t values ('王五','2005-12-03',700)declare @sql varchar(2000)
select @sql='select 姓名'select @sql=@sql+',['+日期+'月份销售名次]
=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售名次 end),(select top 1 count(1) from t group by 姓名 order by count(*) desc)),
['+日期+'月份销售金额]=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售金额 end),0),['+日期+'月份销售次数]
=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售次数 end),0)'
from (select 姓名,convert(char(7),日期,120) as 日期,销售金额 from t) a
group by 日期
select @sql=@sql+' from
(select *,销售名次=(select count(1) from 
(select 姓名,convert(char(7),日期,120) as 日期,sum(销售金额) as 销售金额,count(1) as 销售次数
from t group by 姓名,convert(char(7),日期,120)) a where a.日期=b.日期 and a.销售金额>=b.销售金额) from
(select 姓名,convert(char(7),日期,120) as 日期,sum(销售金额) as 销售金额,count(1) as 销售次数
from t group by 姓名,convert(char(7),日期,120)) b) a group by 姓名'exec(@sql)
drop table t
姓名                   2005-11月份销售名次 2005-11月份销售金额 2005-11月份销售次数 2005-12月份销售名次 2005-12月份销售金额 2005-12月份销售次数 
-------------------- ------------- ------------- ------------- ------------- ------------- ------------- 
李四                   2             150           1             2             400           1
王五                   3             0             0             1             700           1
张三                   1             300           2             3             300           1警告: 聚合或其它 SET 操作消除了空值。

Answer 2

有点小问题，改了一下，现在不会出现有相同销售金额而无法排名次的问题了！create table t
(姓名 varchar(20),日期 datetime,销售金额 int)insert t values ('张三','2005-11-01',100)
insert t values ('张三','2005-11-02',200)
insert t values ('李四','2005-11-03',150)
insert t values ('张三','2005-12-01',300)
insert t values ('李四','2005-12-02',400)
insert t values ('王五','2005-12-03',700)declare @sql varchar(2000)
select @sql='select 姓名'select @sql=@sql+',['+日期+'月份销售名次]
=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售名次 end),(select top 1 count(1) from t group by 姓名 order by count(*) desc)),
['+日期+'月份销售金额]=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售金额 end),0),['+日期+'月份销售次数]
=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售次数 end),0)'
from (select 姓名,convert(char(7),日期,120) as 日期,销售金额 from t) a
group by 日期select 姓名,id=identity(int,1,1),convert(char(7),日期,120) as 日期,sum(销售金额) as 销售金额
,count(1) as 销售次数 into # from t group by 姓名,convert(char(7),日期,120) order by sum(销售金额)select @sql=@sql+' from
(select *,销售名次=(select count(1) from # a where a.日期=b.日期 and a.id>=b.id) from # b) b group by 姓名' exec(@sql)
drop table #
drop table t姓名                   2005-11月份销售名次 2005-11月份销售金额 2005-11月份销售次数 2005-12月份销售名次 2005-12月份销售金额 2005-12月份销售次数 
-------------------- ------------- ------------- ------------- ------------- ------------- ------------- 
李四                   2             150           1             3             400           1
王五                   3             0             0             2             700           1
张三                   1             300           2             1             300           1警告: 聚合或其它 SET 操作消除了空值。

Answer 3

samfeng_2003(凤翼天翔)这种精神真是可贵^_^
学习！

Answer 4

楼上这样说可真折杀小弟了！昨天刚刚安宽带，兴奋了一下而已！:)
楼主的名次问题，其实还是有问题，又改了一下要好点，但是怕出现重复的第一名，那样就不会出现第二名了！
declare @sql varchar(2000)
select @sql='select 姓名'select @sql=@sql+',['+日期+'月份销售名次]
=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售名次 end),(select count(*) from t where convert(char(7),日期,120)='''+日期+''')),
['+日期+'月份销售金额]=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售金额 end),0),['+日期+'月份销售次数]
=isnull(sum(case when convert(char(7),日期,120)='''+日期+'''
then 销售次数 end),0)'
from (select 姓名,convert(char(7),日期,120) as 日期,销售金额 from t) a
group by 日期
select @sql=@sql+' from
(select *,销售名次=(select count(1) from 
(select 姓名,convert(char(7),日期,120) as 日期,sum(销售金额) as 销售金额,count(1) as 销售次数
from t group by 姓名,convert(char(7),日期,120)) a where a.日期=b.日期 and a.销售金额>=b.销售金额) from
(select 姓名,convert(char(7),日期,120) as 日期,sum(销售金额) as 销售金额,count(1) as 销售次数
from t group by 姓名,convert(char(7),日期,120)) b) a group by 姓名'exec(@sql)
drop table t

Answer 5

学习,太牛了,
请教各位有什么关于动态SQL的书或资料下载啊,经典点的

Answer 6

create table abc([name] varchar(16),[date] datetime,[money] decimal(28,0))
insert into abc
select '张三',   '2005-11-01',       100 union all
select '张三',   '2005-11-02',       200 union all
select '李四',   '2005-11-03',       150 union all
select '张三',   '2005-12-01',       300 union all
select '李四',   '2005-12-02',       400 union all
select '王五',   '2005-12-03',       700
declare @loop int
declare @currentLoop int
set @currentLoop=1
declare @currentYearMonth char(6)
select @currentYearMonth=(
select top 1 convert(char(6),[date],112)
from abc group by convert(char(6),[date],112)order by convert(char(6),[date],112) )
select @loop=datediff(m,min([date]),max([date]))+1 from abccreate table temp2(YearMonth char(6),[name]varchar(16),[money]decimal(28,0),sailCount int,sort int)
while @currentLoop<=@loop
begin
create table temp1(YearMonth char(6),[name]varchar(16),[money]decimal(28,0),sailCount int,sort int identity)
insert into temp1
select @currentYearMonth,[name],[money]=sum([money]),sailCount=count([money])from abc where convert(char(6),[date],112)=@currentYearMonth
group by [name],convert(char(6),[date],112)order by [money]desc
select * from temp1
insert into temp2 select * from temp1
drop table temp1
set @currentLoop=@currentLoop+1
set @currentYearMonth=convert(char(6),dateadd(m,1,@currentYearMonth+'01'),112)
end
declare @sql varchar(8000)
set @sql=''
select @sql='select a.[name]as 姓名'-- from (select [name] from abc group by [name])a'
set @currentLoop=1
select @currentYearMonth=(
select top 1 convert(char(6),[date],112)
from abc group by convert(char(6),[date],112)order by convert(char(6),[date],112) )
while @currentLoop<=@loop
begin
set @sql=@sql+','''+@currentYearMonth+'名次''=isnull(b'+cast(@currentLoop as varchar)+'.sort,0)'
+','''+@currentYearMonth+'销售金额''=isnull(b'+cast(@currentLoop as varchar)+'.[money],0)'
set @currentLoop=@currentLoop+1
set @currentYearMonth=convert(char(6),dateadd(m,1,@currentYearMonth+'01'),112)
end
set @sql=@sql+' from (select [name] from abc group by [name])a '
set @currentLoop=1
select @currentYearMonth=(
select top 1 convert(char(6),[date],112)
from abc group by convert(char(6),[date],112)order by convert(char(6),[date],112) )
while @currentLoop<=@loop
begin
set @sql=@sql+'left join (select * from temp2 where YearMonth='''+@currentYearMonth+''')b'+cast(@currentLoop as varchar)
+' on a.[name]=b'+cast(@currentLoop as varchar)+'.[name] '
set @currentLoop=@currentLoop+1
set @currentYearMonth=convert(char(6),dateadd(m,1,@currentYearMonth+'01'),112)
endexec(@sql)
drop table abc,temp2/*
姓名               200511名次    200511销售金额                     200512名次    200512销售金额                     
---------------- ----------- ------------------------------ ----------- ------------------------------ 
李四               2           150                            2           400
王五               0           0                              1           700
张三               1           300                            3           300
*/

Answer 7

上面有一句注释的删除掉！
-- from (select [name] from abc group by [name])a'还有一句
select * from temp1
也可以删除，那是我测试时看的