业务需求:
最近的问题就是,有很多部门,部门又拥有商品,商品有不同的品种,我们需要计算出不同部门里有多少不同商品,
但是对一个部门来说 既有属于部门的商品,也有属于个人的商品,这也就是两个表,
首先,我们知道,我们需要查的就是部门中同一个商品,个人持有量和部门持有量。
SELECT
ri_equipmentmessage_sparepart.id,
ri_equipmentmessage_sparepart.`name`,
ri_equipmentmessage_sparepart.model,
ri_equipmentmessage_sparepart.brand,
ri_equipment_sparepart.sparepart_number AS number,
ri_equipmentmessage_equipment.department
FROM
ri_equipment_sparepart
INNER JOIN ri_equipmentmessage_sparepart ON ri_equipment_sparepart.sparepart_id = ri_equipmentmessage_sparepart.id
INNER JOIN ri_equipmentmessage_equipment ON ri_equipment_sparepart.equipment_id = ri_equipmentmessage_equipment.id
UNION ALL
SELECT
ri_equipmentmessage_sparepart.id,
ri_equipmentmessage_sparepart.`name`,
ri_equipmentmessage_sparepart.model,
ri_equipmentmessage_sparepart.brand,
ri_department_sparepart.number,
ri_department_sparepart.departmentId AS department
FROM
ri_department_sparepart
INNER JOIN ri_equipmentmessage_sparepart ON ri_department_sparepart.sparePartId = ri_equipmentmessage_sparepart.id
上面是部门持有量,后面是个人持有量,通过union all联合在一起成为一个新的表,注意两个查询字段顺序必须一致,并且数据类型也必须一致,字段可以隐式转换。
SELECT
id,
NAME,
model,
brand,
SUM(number),
department
FROM
(
SELECT
ri_equipmentmessage_sparepart.id,
ri_equipmentmessage_sparepart.`name`,
ri_equipmentmessage_sparepart.model,
ri_equipmentmessage_sparepart.brand,
ri_equipment_sparepart.sparepart_number AS number,
ri_equipmentmessage_equipment.department
FROM
ri_equipment_sparepart
INNER JOIN ri_equipmentmessage_sparepart ON ri_equipment_sparepart.sparepart_id = ri_equipmentmessage_sparepart.id
INNER JOIN ri_equipmentmessage_equipment ON ri_equipment_sparepart.equipment_id = ri_equipmentmessage_equipment.id
UNION ALL
SELECT
ri_equipmentmessage_sparepart.id,
ri_equipmentmessage_sparepart.`name`,
ri_equipmentmessage_sparepart.model,
ri_equipmentmessage_sparepart.brand,
ri_department_sparepart.number,
ri_department_sparepart.departmentId AS department
FROM
ri_department_sparepart
INNER JOIN ri_equipmentmessage_sparepart ON ri_department_sparepart.sparePartId = ri_equipmentmessage_sparepart.id
) AS a
GROUP BY
department,
id
然后,根据部门和id进行分组,就可以了,注意 衍生表必须有 as 表明