从一个表中求和
表结构如下,,id,type,point,staff_id,checkdate
1,'公共内容',5,'0000','2007-1-1'
2,'业务内容',4,'0000','2007-1-1'
3,'业务内容',2,'0000','2007-1-2'
4,'公共科目',2,'0000','2007-1-3'求得结果如下staff_id,公共内容,业务内容,公共科目,业务科目,领导加分
'0000',5,6,2,0,0
得有条件 checkdate >=2007-1-1 <2007-1-7除了用full outer join 还有什么列简单的语句不,,不能用游标和临时表
表结构如下,,id,type,point,staff_id,checkdate
1,'公共内容',5,'0000','2007-1-1'
2,'业务内容',4,'0000','2007-1-1'
3,'业务内容',2,'0000','2007-1-2'
4,'公共科目',2,'0000','2007-1-3'求得结果如下staff_id,公共内容,业务内容,公共科目,业务科目,领导加分
'0000',5,6,2,0,0
得有条件 checkdate >=2007-1-1 <2007-1-7除了用full outer join 还有什么列简单的语句不,,不能用游标和临时表
from table
group by staff_id
staff_id,
SUM(Case type When N'公共内容' Then point Else 0 End) As 公共内容,
SUM(Case type When N'业务内容' Then point Else 0 End) As 业务内容,
SUM(Case type When N'公共科目' Then point Else 0 End) As 公共科目,
SUM(Case type When N'业务科目' Then point Else 0 End) As 业务科目,
SUM(Case type When N'领导加分' Then point Else 0 End) As 领导加分
From
TableName
Where checkdate >= '2007-1-1' And checkdate < '2007-1-7'
Group By staff_id
select 1,'公共内容',5,'0000','2007-1-1' union
select 2,'业务内容',4,'0000','2007-1-1' union
select 3,'业务内容',2,'0000','2007-1-2' union
select 4,'公共科目',2,'0000','2007-1-3' union
select 5,'业务科目',0,'0000','2007-1-2' union
select 6,'领导加分',0,'0000','2007-1-3'
declare @str varchar(8000)
set @str='select staff_id ,'
select @str=@str+quotename(rtrim(type))+'=sum(case when type='''+rtrim(type)+''' then point else 0 end),'
from ta group by type order by type
select @str=left(@str, len(@str)-1) ,@str=@str+' from tA where checkdate >='+'''2007-1-1 ''' +'and checkdate<'+'''2007-1-7'''+ 'group by staff_id order by staff_id '
exec(@str)不知道是否是你要的结果?