数据库组成为 ID,人数
ID的为6位字符串,头两位为学校ID,中间两位为年级ID,后两位为班级ID
要求做一个视图,能够直接检索所有学校,年级人数。例如数据库数据如下
ID 人数
010101 20
010102 20
010201 20
010202 20
010203 20做出的视图数据如下
ID 人数
01 100
0101 40
0102 60第一条数据表示学校01有学生100人,第二条表示学校01的年级01有学生40人...
追加说明:举例中ID只有3层,实际中可能有10层,这个怎么做,求教了。
ID的为6位字符串,头两位为学校ID,中间两位为年级ID,后两位为班级ID
要求做一个视图,能够直接检索所有学校,年级人数。例如数据库数据如下
ID 人数
010101 20
010102 20
010201 20
010202 20
010203 20做出的视图数据如下
ID 人数
01 100
0101 40
0102 60第一条数据表示学校01有学生100人,第二条表示学校01的年级01有学生40人...
追加说明:举例中ID只有3层,实际中可能有10层,这个怎么做,求教了。
select
sum(case when left(id,2)='01' then 1 else 0 end),
sum(case when left(id,4)='0101' then 1 else 0 end),
sum(case when left(id,6)='0102' then 1 else 0 end)
from tb
select
sum(case when left(id,2)='01' then 1 else 0 end),
sum(case when left(id,4)='0101' then 1 else 0 end),
sum(case when left(id,4)='0102' then 1 else 0 end)
from tb
--10行的话改下动态sql
010101 20
010102 20
010201 20
010202 20
010203 20--
Create view v_table1
asselect ID,sum(人数) as 人数 from tabe1 group by ID
union all
select left(ID,4) as ID,sum(人数) as 人数 from tabe1 group by left(ID,4)
union all
select left(ID,2) as ID,sum(人数) as 人数 from tabe1 group by left(ID,2)
insert into tb select '010101',20
insert into tb select '010102',20
insert into tb select '010201',20
insert into tb select '010202',20
insert into tb select '010203',20
go
select a.id,SUM(b.人数)人数 from (
select distinct LEFT(ID,2) as id from tb union all select distinct LEFT(id,4) from tb
)a inner join tb b on b.ID like a.id +'%'
group by a.id
/*
id 人数
-------- -----------
01 100
0101 40
0102 60(3 行受影响)
*/
go
drop table tb
set @i=2
--创建临时表,用于保存最终结果
if object_id('tempdb.dbo.#result') is not null
drop table dbo.#result
create table #result
(
[id] varchar(6),
[人数] int
)while @i<6--可根据自己的需要修改6为你想要的数字
begin
insert into #result
select substring(id,1,@i),sum(人数)
from tb
group by substring(id,1,@i)
set @i=@i+2
end
select * from #result