请教一个SQL的写法，谢谢

[daviddivad]

一共4个表，为了简单易懂，用拼音：

资产表（zichan）,资产类别表(zclb),部门表(bm)，保管人员表(bgry)

zichan:
zichanmingcheng(资产名称)
zclbID(资产类别ID)
baoguanrenID(保管人ID)

zclb:
leibiemingcheng(类别名称)
leibieID(资产类别ID)

bm:
bmID(部门id)
bmmingcheng(部门名称)

bgry:
ryID(保管人ID)
bgrmingcheng(保管人名称)
bumenID(部门ID)

现在要按部门分类统计资产情况，结果要以下几个字段
资产类别 资产名称 部门 数量

请教一下SQL语句写法，对group by 和left join有点忘记了，，，，

[deem302]

select 资产类别,资产名称,部门名称,count(*) from zichan a join bgryb on a.保管人ID=.b.保管人ID inner join bm c on b.部门ID=c.部门id group by 资产类别,资产名称,部门名称

[zrssweet]

SELECT ZCLB.leibiemingcheng AS 资产类别 ,ZICHAN.zichanmingcheng AS 资产名称 ,BM.bmmingcheng AS 部门 ,COUNT(ZICHAN.zichanmingcheng) FROM tb_ZCLB AS ZCLB LEFT JOIN tb_ZICHAN AS ZICHAN ON ZCLB.leibieID=ZICHAN.zclbID LEFT JOIN tb_BGRY AS BGRY ON BGRY.ryID=ZICHAN.baoguanrenID LEFT JOIN tb_BM AS BM ON BM.ID=BGRY.bumenID GROUP BY ZCLB.leibiemingcheng ,ZICHAN.zichanmingcheng ,BM.bmmingcheng;

[一个烤羊腰子]

SELECT ZCLB.leibiemingcheng AS 资产类别 ,ZICHAN.zichanmingcheng AS 资产名称 ,BM.bmmingcheng AS 部门 ,COUNT(ZICHAN.zichanmingcheng) FROM tb_ZCLB AS ZCLB LEFT JOIN tb_ZICHAN AS ZICHAN ON ZCLB.leibieID=ZICHAN.zclbID LEFT JOIN tb_BGRY AS BGRY ON BGRY.ryID=ZICHAN.baoguanrenID LEFT JOIN tb_BM AS BM ON BM.ID=BGRY.bumenID GROUP BY ZCLB.leibiemingcheng ,ZICHAN.zichanmingcheng ,BM.bmmingcheng; 希望我的回答对你有用！٩(๑❛ᴗ❛๑)۶