mysql部门分类_sql语句统计各部门不同人员类别的人数

展开全部

第一步，依据你上边给的语句创62616964757a686964616fe78988e69d8331333332643337建一个视图createviewv_dept

as

selectbd_deptdoc.deptcodeasdeptcode,

bd_deptdoc.deptlevelasdeptlevel,

bd_deptdoc.deptnameasdeptname,

bd_psndoc.psnnameaspsnname,

bd_psncl.psnclassnameaspsnclassname,

bd_psncl.psnclasscodeaspsnclasscode

frombd_psndoc

innerjoinbd_deptdoc

onbd_psndoc.pk_deptdoc=bd_deptdoc.pk_deptdoc

innerjoinbd_psncl

onbd_psndoc.pk_psncl=bd_psncl.pk_psncl

第二步，动态执行sql，由于你人员类别可能不止就3种，所以要动态执行declare@sqlvarchar(4000)

set@sql='selectdeptcode,deptname'

select@sql=@sql+',sum(isnull(case[psnclassname]when'''+[psnclassname]+'''then1end,0))as

['+[psnclassname]+']'

from(selectdistinct[psnclassname]fromv_dept)asa

select@sql=@sql+'fromv_deptgroupbydeptcode,deptname'

exec(@sql)