department projecttype projectstate time
13 2 1
9 1 2
9 1 2
9 3 1
9 1 3
13 2 4
想得到的结果（其实就是以的department 和projecttype进行分组，统计各个projectstate 状态的数量，还可能以time作为查询条件）
department projecttype 总数 projectstate1 projectstate2 projectstate3 projectstateother（不是1,2,3状态）
9 1 3 0 2 1 0
9 3 1 1 0 0 0
13 2 2 1 0 0 1
自己愚笨，感觉写出来的sql语句效率很低很低，求高人指定有没有高效的sql语句（sql server2008）
select a.department,a.projecttype,number,numb,numc,numd，(number-isnull(numb,0)-isnull(numc,0)-isnull(numd,0)) as othernum, from
(select department,projecttype,count(*) as number from jiraproject(nolock) group by department,projecttype) a
left join
(select department,projecttype,count(*) as numb from jiraproject(nolock) where projectstate=1 group by department,projecttype) b
on a.department=b.department and a.projecttype=b.projecttype
left join
(select department,projecttype,count(*) as numc from jiraproject(nolock) where projectstate=2 group by department,projecttype) c
on a.department=c.department and a.projecttype=c.projecttype
left join
(select department,projecttype,count(*) as numd from jiraproject(nolock) where projectstate=3 group by department,projecttype) d
on a.department=d.department and a.projecttype=d.projecttype
4 个解决方案
#1
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[department] int,
[projecttype] int,
[projectstate] int
)
go
insert [test]
select 13,2,1 union all
select 9,1,2 union all
select 9,1,2 union all
select 9,3,1 union all
select 9,1,3 union all
select 13,2,4
go
declare @str varchar(2000)
set @str=''
select
@str=@str+',[State'+LTRIM([projectstate])+']=sum(case when [projectstate]='
+LTRIM([projectstate])+' then 1 else 0 end)'
from
test
group by
[projectstate]
exec('select [department],[projecttype]'+
@str+',count(1) as Total from test group by [department],[projecttype] order by 1')
/*
department projecttype State1 State2 State3 State4 Total
9 1 0 2 1 0 3
9 3 1 0 0 0 1
13 2 1 0 0 1 2
*/
#2
select department,
总数=count(*),
projectstate1=sum(case when projectstate=1 then 1 else 0 end),
projectstate2=sum(case when projectstate=2 then 2 else 0 end),
projectstate3=sum(case when projectstate=3 then 3 else 0 end),
projectstateother=sum(case when projectstate not in(1,2,3) then 1 else 0 end)
from jiraproject group by department
#3
case when
#4
恩，2楼方法很好，谢了
#1
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[department] int,
[projecttype] int,
[projectstate] int
)
go
insert [test]
select 13,2,1 union all
select 9,1,2 union all
select 9,1,2 union all
select 9,3,1 union all
select 9,1,3 union all
select 13,2,4
go
declare @str varchar(2000)
set @str=''
select
@str=@str+',[State'+LTRIM([projectstate])+']=sum(case when [projectstate]='
+LTRIM([projectstate])+' then 1 else 0 end)'
from
test
group by
[projectstate]
exec('select [department],[projecttype]'+
@str+',count(1) as Total from test group by [department],[projecttype] order by 1')
/*
department projecttype State1 State2 State3 State4 Total
9 1 0 2 1 0 3
9 3 1 0 0 0 1
13 2 1 0 0 1 2
*/
#2
select department,
总数=count(*),
projectstate1=sum(case when projectstate=1 then 1 else 0 end),
projectstate2=sum(case when projectstate=2 then 2 else 0 end),
projectstate3=sum(case when projectstate=3 then 3 else 0 end),
projectstateother=sum(case when projectstate not in(1,2,3) then 1 else 0 end)
from jiraproject group by department
#3
case when
#4
恩，2楼方法很好，谢了