CREATE TABLE #info
(
name nvarchar (10) NULL ,
branch nvarchar (10) NULL ,
age tinyint NULL
)
insert into #info values
('王一','营业部','22')
insert into #info values
('赵二','营业部','18')
insert into #info values
('陈平','营业部','29')
insert into #info values
('李红','科技部','32')
insert into #info values
('周胜','会计部','23')
create table #ainfo
(
Nu tinyint,
ageset1 tinyint,
ageset2 tinyint
)
insert into #ainfo values
(1,1,20)
insert into #ainfo values
(2,21,30)
insert into #ainfo values
(3,31,99)
GO
select
b.Nu,'('+str(b.ageset1,2)+'到'+str(b.ageset2,2)+'之间的人数)',
sum(case when branch='营业部' then 1 else 0 end) 营业部,
sum(case when branch='科技部' then 1 else 0 end) 科技部,
sum(case when branch='会计部' then 1 else 0 end) 会计部
from #info a
inner join #ainfo b ON a.age between ageset1 and ageset2
group by b.Nu,b.ageset1,b.ageset2
提醒一下各位啊如果部门有1000个 就不能这么写了吧??
有没有别的方法?
select
营业部=sum(case when Adepartment='营业部' and age>20 then 1 else 0 end),
科技部=sum(case when Adepartment='科技部' and age>20 then 1 else 0 end),
会计部=sum(case when Adepartment='会计部' and age>20 then 1 else 0 end)
from a
select
sum(case when branch='营业部' and age>20 then 1 else 0 end) 营业部,
sum(case when branch='科技部' and age>20 then 1 else 0 end) 科技部,
sum(case when branch='会计部' and age>20 then 1 else 0 end) 会计部
from dbo.info
select
营业部=sum(case when branch='营业部' and age>20 then 1 else 0 end),
科技部=sum(case when branch='科技部' and age>20 then 1 else 0 end),
会计部=sum(case when branch='会计部' and age>20 then 1 else 0 end)
from info
select
营业部=sum(case when branch='营业部' and age>20 then 1 else 0 end),
科技部=sum(case when branch='科技部' and age>20 then 1 else 0 end),
会计部=sum(case when branch='会计部' and age>20 then 1 else 0 end)
from info
select
营业部=sum(case when Adepartment='营业部' and age>20 then 1 else 0 end),
科技部=sum(case when Adepartment='科技部' and age>20 then 1 else 0 end),
会计部=sum(case when Adepartment='会计部' and age>20 then 1 else 0 end)
from a
select [会计部]=isnull(sum(case Adepartment when '会计部' then 1 end),0),[科技部]=isnull(sum(case Adepartment when '科技部' then 1 end),0),[营业部]=isnull(sum(case Adepartment when '营业部' then 1 end),0) from a where age>20
SQL Server 啊 努力 奋斗-----------------------------------------------------
drop table a
create table a(Aname varchar(10),Adepartment varchar(10),age int )
insert into a select '王一','营业部',22 union all
select '赵二','营业部',18 union all
select '陈平','营业部',29 union all
select '周胜','会计部',23 union all
select '李红','科技部',32
select
营业部=sum(case when Adepartment='营业部' and age>20 then 1 else 0 end),
科技部=sum(case when Adepartment='科技部' and age>20 then 1 else 0 end),
会计部=sum(case when Adepartment='会计部' and age>20 then 1 else 0 end)
from a
SQL Server 啊 努力 奋斗-----------------------------------------------------
create table info(name varchar(10),branch varchar(10),age int)
insert info
select '王一','营业部',22 union all
select '赵二','营业部',18 union all
select '陈平','营业部',29 union all
select '李红','科技部',32 union all
select '周胜','会计部',23
declare @s varchar(8000)
set @s=''
select @s=@s+',['+branch+']=isnull(sum(case branch when '''+ branch + ''' then 1 end),0)' from info group by branch
select @s='select ' +stuff(@s,1,1,'')+' from info where age>20'
select
营业部=sum(case when branch='营业部' and age>20 then 1 else 0 end),
科技部=sum(case when branch='科技部' and age>20 then 1 else 0 end),
会计部=sum(case when branch='会计部' and age>20 then 1 else 0 end)
from
表