mysql - 统计MySQL中的数据

Question

这些是我的表格:

tbl_fa_asset_reg

• Assets ID
• Assets 组
• 业务部门
• Assets 组

tbl_a_comp_f_bu

• 地区
• 区
• 业务部门

tbl_fa_asset_reg_mas_b_class

• 类别代码
• 描述

tbl_fa_asset_reg_mas_c_group

• 编号
• Assets 组
• Assets 类别

这是我的查询:

SELECT DISTINCT a.ClassCode,a.Description,d.BusinessUnit,d.BUDes,d.Department 
    FROM tbl_fa_asset_reg_mas_b_class a 
    JOIN tbl_fa_asset_reg_mas_c_group b ON a.ClassCode=b.AssetClass 
    JOIN tbl_fa_asset_reg c ON c.AssetGroup=b.AssetGroup 
    JOIN tbl_a_comp_f_bu d ON d.BusinessUnit=c.BusinessUnit
    order by a.ClassCode ASC 

我想要的输出:

Answer 1

看来你只需要聚合:

select a.ClassCode,
    a.Description,
    d.BusinessUnit,
    d.BUDes,
    d.Department,
    count(*) as total
from tbl_fa_asset_reg_mas_b_class a
join tbl_fa_asset_reg_mas_c_group b on a.ClassCode = b.AssetClass
join tbl_fa_asset_reg c on c.AssetGroup = b.AssetGroup
join tbl_a_comp_f_bu d on d.BusinessUnit = c.BusinessUnit
group by a.ClassCode,
    a.Description,
    d.BusinessUnit,
    d.BUDes,
    d.Department
order by ClassCode asc