一共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有点忘记了,,,,
资产表(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有点忘记了,,,,
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;希望我的回答对你有用!٩(๑❛ᴗ❛๑)۶
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;