高分请教一个SQL查询语句,需求已变更

[boyz]

表info，有字段： name，branch，age
表中有数据：
王一，营业部，22
赵二，营业部，18
陈平，营业部，29
李红，科技部，32
周胜，会计部，23

_______________________________________
表 ageinfo
字段如下
no 编号
ageset 年龄段

里面有记录
1 20
2 30

————————————————————————
要求统计出不同部门，不同年龄段的人数

查询结果如下


营业部 科技部 会计部
----------- ----------- -----------
（20岁以下人数） 1 0 0
（30岁以下人数） 2 1 1

[happyamei]

学习中......

[happyamei]

好像只有 zlp321002(她是我的唯一.) 能够实现如图所示

[OracleRoob]

不知道楼主是需要动态的生成交叉表，还是象1楼那样的静态的SQL语句

按1楼的写法，只能有这三个部门，如果还有其他部门动态添加，需要时刻修改SQL语句

[boyz]

希望是可以生成 动态交叉表，这样查询次数会做到最小

按照一楼的方法，只能先做查询后把SQL语句拼接起来

担心如果语句过长可能会有问题

[cen123]

Create table info
(
name VARCHAR(50) NOT NULL,
branch VARCHAR(30) NOT NULL,
age INT NOT NULL
)

Create table ageInfo
(
no INT IDENTITY(1,1) NOT NULL,
ageset INT NOT NULL,
CONSTRAINT ID_PK PRIMARY KEY (no)
)

GO
INSERT info SELECT '王一','营业部',22
UNION SELECT '赵二','营业部',18
UNION SELECT '陈平','营业部',29
UNION SELECT '李红','科技部',32
UNION SELECT '周胜','会计部',23

INSERT ageInfo SELECT 20
UNION SELECT 30

SELECT TOP 1 (SELECT COUNT(*) FROM info WHERE branch='营业部' and 20<age and age<30) AS '营业部',
(SELECT COUNT(*) FROM info WHERE branch='科技部' and 20<age and age<30) AS '科技部',
(SELECT COUNT(*) FROM info WHERE branch='会计部' and 20<age and age<30) AS '会计部'
FROM info t WHERE t.age<30 AND t.age>20
UNION
SELECT TOP 1 (SELECT COUNT(*) FROM info WHERE branch='营业部' and age<20) AS '营业部',
(SELECT COUNT(*) FROM info WHERE branch='科技部' and age<20) AS '科技部',
(SELECT COUNT(*) FROM info WHERE branch='会计部' and age<20) AS '会计部'
FROM info t1 WHERE t1.age<20

DROP TABLE info
DROP TABLE ageInfo
GO

[zhouhaihe]

这样也不是动态的啊
帮顶

[zhaoanle]

--测试数据
create table info(name varchar(10),branch varchar(10),age int)
insert info values('王一','营业部',22)
insert info values('赵二','营业部',18)
insert info values('陈平','营业部',29)
insert info values('李红','科技部',32)
insert info values('周胜','会计部',23)

create table ageinfo(ageset int,年龄段 int)
insert ageinfo values(1,20)
insert ageinfo values(2,30)
insert ageinfo values(3,40)

--查询
declare cur cursor for select 年龄段 from ageinfo
declare @sql varchar(8000)
declare @age varchar(3)
declare @firstage varchar(3)
set @sql=''
open cur
fetch next from cur into @age
set @firstage=@age

while @@FETCH_STATUS =0
begin
select @sql=@sql+'sum(case when branch='''+a.branch +''' and age<'''+ @age +'''then 1 else 0 end) '''+a.branch+''',' from (select distinct branch from info) a
fetch next from cur into @age
set @sql=left(@sql,len(@sql)-1) + ' from info union select '''+@age+'岁以下人数'' ''类型'','
end
set @sql='select '''+@firstage+'岁以下人数'' ''类型'',' + left(@sql,len(@sql)-29)
close cur
deallocate cur
exec(@sql)
--结果
类型 会计部 科技部 营业部
------------ ----------- ----------- -----------
20岁以下人数 0 0 1
30岁以下人数 1 0 3
40岁以下人数 1 1 3

[maimaizhi]

alter table ageinfo add column starAge int,endAge int
update ageinfo set starAge=0,endAge=20 where ageset=20
update ageinfo set starAge=20,endAge=30 where ageset=30
select b.ageset 年龄段,a.branch 部门,Count(*) 数量 from info a，ageinfo b
where a.age>b.strAge and a.age<=b.endAge group by b.ageset,a.branch

[zlp321002]

select
类型='20岁以下人数',
营业部=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
union all
select
类型='30岁以下人数',
营业部=sum(case when branch='营业部' and age<30 then 1 else 0 end),
科技部=sum(case when branch='科技部' and age<30 then 1 else 0 end),
会计部=sum(case when branch='会计部' and age<30 then 1 else 0 end)
from info

[了缘]

select 20 as ageflag,branch,count(name) from info where age<=20 group by branch
union all
select 30 as ageflag,branch,count(name) from info where age<=30 and age<20 group by branch

[byn]

select no=no,startage=ageset,endage=ageset into #tmpage from ageinfo

update t
set endage = (select coalesce(min(startage),t.endage) from #tmpage t2 where t2.startage > t.startage )
from #tmpage t

select t.no,i.branch into #tmp from info i
left outer join #tmpage t on i.age between t.startage and t.endage

select t.no,t.branch,count(t.no) from #tmp t group by t.no,t.branch order by t.no

[47522341]

select 20 as ageflag,branch,count(name) from info where age<=20 group by branch
union all
select 30 as ageflag,branch,count(name) from info where age<=30 and age<20 group by branch