sql优化
SELECT
t.id AS id,
t.count AS count,
t.basenum AS bnum,
c1.cst_name AS NAME,
v.vendor_name AS vname,
m.name AS mname,
c1.cst_code AS cstcode,
c1.wrap_name AS wrapname
FROM
(
SELECT
d.id AS id,
d.hospital_consumable_code AS hcode,
c.status AS status,
s.id AS sid,
d.base_num AS basenum,
COUNT(*) AS count
FROM
consumables_inventory c
LEFT JOIN department_store_house d ON c.department_store_house_code = d.code
LEFT JOIN store_house s ON s.code = d.store_house_code
WHERE
STATUS = 3
AND s.id = 1
GROUP BY
id,
hcode,
status,
sid,
basenum
– HAVING
– status=3 AND sid = 1
) AS t
LEFT JOIN hospital_consumable h ON h.code = t.hcode
LEFT JOIN consumables_dict c1 ON h.consumables_dict_code = c1.code
LEFT JOIN vendor_dict v ON v. CODE = c1.vendor_dict_code
LEFT JOIN manufacturer m ON m. CODE = c1.manufacturer_code
WHERE
t.count < t.basenum
LIMIT 0,10
在groupBy之前写上where会加快速度