Select 年份=Left(Convert(varChar(10),b.加分日期,120),4),a.姓名,
1月=Sum(case when Month(b.加分日期)=1 then b.加分值 else 0),
2月=Sum(case when Month(b.加分日期)=2 then b.加分值 else 0),
.....
12月=Sum(case when Month(b.加分日期)=12 then b.加分值 else 0)
from 表一 a,表二 b
where a.人员编号*=b.人员编号
Group by b.姓名,Left(Convert(varChar(10),b.加分日期,120),4)
1月=Sum(case when Month(b.加分日期)=1 then b.加分值 else 0),
2月=Sum(case when Month(b.加分日期)=2 then b.加分值 else 0),
.....
12月=Sum(case when Month(b.加分日期)=12 then b.加分值 else 0)
from 表一 a,表二 b
where a.人员编号*=b.人员编号
Group by b.姓名,Left(Convert(varChar(10),b.加分日期,120),4)
解决方案 »
- 【sql随机问题】随机选择若干行的sql语句
- 请教一个SQL问题
- 兄弟们,给你们下跪了!急啊!再线等个小问提
- 急求高手回复这个问题
- 有三个字段,T1, T2, T3,请问怎么返回一个字段,值是这三个字段的最大值?
- 我要鎖一個表的某一行,要怎麼做?
- 如何选择一个数据集中从第n1到n2条之间的记录?
- 一个交差表的难题,哪位帮忙看看,怎么解决,给一佰分.
- PowerDesigner 9中怎样设置identity???
- where can download oracle 8i personal server
- 我在我的程序里创建了一个临时表#t,然后我在sql server 2000查询分析器里"select * from #t"确得到提示“对象名 '#t' 无效。”why?
- 怎么样才能从数据库中查询20到30中的记录?
Sum(case when Month(b.加分日期)=1 then b.加分值 else 0),
Sum(case when Month(b.加分日期)=2 then b.加分值 else 0),
.....
Sum(case when Month(b.加分日期)=12 then b.加分值 else 0)
from 表一 a left join 表二 b on a.人员编号 = b.人员编号
Group by b.姓名,year(b.加分日期)
select @i=0,@sql = 'select 姓名',@sql2=''
select @i=@i+1,@sql = @sql + ',max(case datepart(加分日期) when '''+cast(@i as varchar(10))+''' then 加分值 end) ['+cast(@i as varchar(10))+'月]'
,@sql2=@sql2+',cast(sum('+cast(@i as varchar(10))+'月) as varchar(100))'
from 表二
select @sql = @sql+' into #a from (select a.姓名,b.加分值,b.加分日期 from 表一 a,表二 b where a.人员编号=b.人员编号) tem group by 姓名'exec(@sql+' select * from #a union all select ''合计'''+@sql2+' from #a')go
insert into #a1
select '001','小李'
union all
select '002','小明'
create table #a2(加分编号 int,人员编号 varchar(50),加分值 int,加分日期 datetime)
insert into #a2
select 1,'001',2,'2001/1/1'
union all
select 2,'002',5,'2001/8/10'
union all
select 3,'001',3,'2001/5/9'
union all
select 4,'001',4,'2001/1/4'
union all
select 5,'002',1,'2002/6/12'declare @sql varchar(8000),@i int,@sql2 varchar(8000)
select @i=1,@sql = 'select 姓名',@sql2=''
while @i<=12
begin
select @sql = @sql + ',max(case datepart(m,加分日期) when '''+cast(@i as varchar(10))+''' then 加分值 end) ['+cast(@i as varchar(10))+'月]'
,@sql2=@sql2+',cast(sum(['+cast(@i as varchar(10))+'月]) as varchar(100))'
set @i =@i +1
end
select @sql = @sql+' into #a from (select a.姓名,b.加分值,b.加分日期 from #a1 a,#a2 b where a.人员编号=b.人员编号) tem group by 姓名'exec(@sql+' select * from #a union all select ''合计'''+@sql2+' from #a')godrop table #a1,#a2
Sum(case when Month(b.加分日期)=1 then b.加分值 else 0),
Sum(case when Month(b.加分日期)=2 then b.加分值 else 0),
.....
Sum(case when Month(b.加分日期)=12 then b.加分值 else 0)
from 表一 a left join 表二 b on a.人员编号 = b.人员编号
Group by b.姓名,year(b.加分日期)
insert into #a1
select '001','小李'
union all
select '002','小明'
create table #a2(加分编号 int,人员编号 varchar(50),加分值 int,加分日期 datetime)
insert into #a2
select 1,'001',2,'2001/1/1'
union all
select 2,'002',5,'2001/8/10'
union all
select 3,'001',3,'2001/5/9'
union all
select 4,'001',4,'2001/1/4'
union all
select 5,'002',1,'2002/6/12'declare @sql varchar(8000),@i int,@sql2 varchar(8000)
select @i=1,@sql = 'select 姓名',@sql2=''
while @i<=12
begin
select @sql = @sql + ',isnull(sum(case datepart(m,加分日期) when '''+cast(@i as varchar(10))+''' then 加分值 end),0) ['+cast(@i as varchar(10))+'月]'
,@sql2=@sql2+',cast(sum(['+cast(@i as varchar(10))+'月]) as varchar(100))'
set @i =@i +1
end
select @sql = @sql+' into #a from (select a.姓名,b.加分值,b.加分日期 from #a1 a,#a2 b where a.人员编号=b.人员编号 and datepart(yy,加分日期)=2001) tem group by 姓名'exec(@sql+' select * from #a union all select ''合计'''+@sql2+' from #a')godrop table #a1,#a2
,sum(case 月份 when 1 then 加分值 end) as [1月]
,sum(case 月份 when 2 then 加分值 end) as [2月]
,sum(case 月份 when 3 then 加分值 end) as [3月]
,sum(case 月份 when 4 then 加分值 end) as [4月]
,sum(case 月份 when 5 then 加分值 end) as [5月]
,sum(case 月份 when 6 then 加分值 end) as [6月]
,sum(case 月份 when 7 then 加分值 end) as [7月]
,sum(case 月份 when 8 then 加分值 end) as [8月]
,sum(case 月份 when 9 then 加分值 end) as [9月]
,sum(case 月份 when 10 then 加分值 end) as [10月]
,sum(case 月份 when 11 then 加分值 end) as [11月]
,sum(case 月份 when 12 then 加分值 end) as [12月]
(
select a.人员编号,a.姓名,b.加分值,month(b.加分日期) as 月份
from 表一 a inner join 表二 b on a.人员编号=b.人员编号
) a group by 人员编号