sql语句统计各部门不同人员类别的人数
部门档案：部门编码deptcode /部门名称deptname /与人员档案连接主键pk_deptdoc
人员档案：人员编码psncode /人员名称psnname /与人员类别连接主键pk_psncl
人员类别：人员类别编码psnclasscode /人员类别名称psnclassname
三表了解查询结果如下图：
sql语句如下：
select bd_deptdoc.deptcode as deptcode,
bd_deptdoc.deptlevel as deptlevel,
bd_deptdoc.deptname as deptname,
bd_psndoc.psnname as psnname,
bd_psncl.psnclassname as psnclassname,
bd_psncl.psnclasscode as psnclasscode
from bd_psndoc
inner join bd_deptdoc
on bd_psndoc.pk_deptdoc = bd_deptdoc.pk_deptdoc
inner join bd_psncl
on bd_psndoc.pk_psncl = bd_psncl.pk_psncl
希望最终实现如下图结果：
各位大神们，能教教我么？谢谢！
最新回答
顾北凉城
2026-10-23 12:13:01
第一步，依据你上边给的语句创建一个视图
create view v_dept
as
select bd_deptdoc.deptcode as deptcode,
bd_deptdoc.deptlevel as deptlevel,
bd_deptdoc.deptname as deptname,
bd_psndoc.psnname as psnname,
bd_psncl.psnclassname as psnclassname,
bd_psncl.psnclasscode as psnclasscode
from bd_psndoc
inner join bd_deptdoc
on bd_psndoc.pk_deptdoc = bd_deptdoc.pk_deptdoc
inner join bd_psncl
on bd_psndoc.pk_psncl = bd_psncl.pk_psncl
第二步，动态执行sql，由于你人员类别可能不止就3种，所以要动态执行
declare @sql varchar(4000)
set @sql = 'select deptcode,deptname'
select @sql = @sql + ',sum(isnull(case [psnclassname] when '''+[psnclassname]+''' then 1 end,0)) as
['+[psnclassname]+']'
from (select distinct [psnclassname] from v_dept) as a
select @sql = @sql+' from v_dept group by deptcode,deptname'
exec(@sql)
另类收藏者
2026-03-25 09:55:37
两个步骤：
1：拼接动态SQL
2：将拼接后的动态SQL 组装进行查询，你可以看看我的这个回答，基本上就都能解决你的问题了！
参考：
http://zhidao.baidu.com/question/1366412130583725619.html?oldq=1
初时模样
2026-06-12 13:46:30
晚街听风
2026-04-07 13:55:17
热门标签
