--测试数据
Create table t1 (DeptID varchar(10),Name varchar(20))
Create table t2 (DeptID varchar(10),DeptName varchar(20),DeptCode varchar(10))
insert t1
select '001','haha'
union select '002','hehe'
union select '003','hoho'
union select '004','lele'
union select '002','baba'
union select '004','wywy'insert t2
select '001','技术部','01'
union select '002','技术部一室','0101'
union select '003','技术部二室','0102'
union select '004','科研部','02'select * from t1
select * from t2--语句
select a.DeptCode,b.DeptName,a.total
from
(select left(m.DeptCode,2) as DeptCode,count(1) as total
from
(select t1.DeptID as DeptID1,t1.Name,
t2.DeptCode,t2.DeptID as DeptID2,t2.DeptName
from t2 inner join t1 on t1.DeptID=t2.DeptID) m
Group by left(m.DeptCode,2)) a
join t2 b on a.DeptCode=b.DeptCode--测试结果DeptCode DeptName total
-----------------------
01 技术部 4 02 科研部 2
Create table t1 (DeptID varchar(10),Name varchar(20))
Create table t2 (DeptID varchar(10),DeptName varchar(20),DeptCode varchar(10))
insert t1
select '001','haha'
union select '002','hehe'
union select '003','hoho'
union select '004','lele'
union select '002','baba'
union select '004','wywy'insert t2
select '001','技术部','01'
union select '002','技术部一室','0101'
union select '003','技术部二室','0102'
union select '004','科研部','02'select * from t1
select * from t2--语句
select a.DeptCode,b.DeptName,a.total
from
(select left(m.DeptCode,2) as DeptCode,count(1) as total
from
(select t1.DeptID as DeptID1,t1.Name,
t2.DeptCode,t2.DeptID as DeptID2,t2.DeptName
from t2 inner join t1 on t1.DeptID=t2.DeptID) m
Group by left(m.DeptCode,2)) a
join t2 b on a.DeptCode=b.DeptCode--测试结果DeptCode DeptName total
-----------------------
01 技术部 4 02 科研部 2
from (select left(b.部门序号,2) 部门序号,count(*) 人数
from table1 a,table2 b
where a.部门ID=b.部门ID group by left(b.部门序号,2)) c
[DeptName]=(select DeptName from t2 where left(a.DeptCode,2)=DeptCode),
sum(1)
from
(select t1.DeptID as DeptID1,t1.Name,
t2.DeptCode,t2.DeptID as DeptID2,t2.DeptName
from t2 inner join t1 on t1.DeptID=t2.DeptID) a
group by left(a.DeptCode,2)
from table2 a
join table2 b on b.部门序号 like a.部门序号+'%'
join table1 c on b.部门ID=c.部门ID
where len(a.部门序号)=2
group by a.部门序号,a.部门名
insert table1 select 1,'XX'
union all select 2,'XX'
union all select 2,'XX'
union all select 3,'XX'
union all select 4,'XX'
union all select 4,'XX'
union all select 2,'XX'create table table2(部门ID int,部门名 varchar(10),部门序号 varchar(10))
insert table2 select 1,'技术部','01'
union all select 2,'技术部一室','0101'
union all select 3,'技术部二室','0102'
union all select 4,'科研部','02'
go--统计
select a.部门序号,a.部门名,人数=count(c.部门ID)
from table2 a
join table2 b on b.部门序号 like a.部门序号+'%'
join table1 c on b.部门ID=c.部门ID
where len(a.部门序号)=2
group by a.部门序号,a.部门名
godrop table table1,table2/*--测试结果部门序号 部门名 人数
---------- ---------- -----------
01 技术部 5
02 科研部 2(所影响的行数为 2 行)
--*/