多表相加同一字段union联合的使用

业务需求:

最近的问题就是，有很多部门，部门又拥有商品，商品有不同的品种，我们需要计算出不同部门里有多少不同商品，

但是对一个部门来说 既有属于部门的商品，也有属于个人的商品，这也就是两个表，

首先，我们知道，我们需要查的就是部门中同一个商品，个人持有量和部门持有量。

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 表明