sql 数据优化 union 代替or

weixin_30549175

于 2019-07-06 02:50:58 发布

阅读量146

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhanghonjiang2011/archive/2011/12/22/2297924.html

本文详细解析了一段复杂的SQL查询代码，该查询旨在统计药品供应商的数量，涉及多个表的联接、子查询以及数据聚合。通过对这段代码的解读，读者可以深入理解如何使用SQL进行高级数据操作。

select t.name,sum(t.SupplierCount) from (
select a.name,count(b.Supplier_Id) as SupplierCount
from yxs_administrators a,yxs_drugsbase_supplier b ,yxs_product c,yxs_supplies d,yxs_drugsbase e
where b.bstop=0 and a.adminid=b.editer and c.Supplier_Id=b.Supplier_Id and b.supplies_id=d.supplies_id and b.drugsbase_id=e.drugsbase_id
and b.agent_price=0
group by a.name
UNION all
select a.name,count(b.Supplier_Id) as SupplierCount
from yxs_administrators a,yxs_drugsbase_supplier b ,yxs_product c,yxs_supplies d,yxs_drugsbase e
where b.bstop=0 and a.adminid=b.editer and c.Supplier_Id=b.Supplier_Id and b.supplies_id=d.supplies_id and b.drugsbase_id=e.drugsbase_id
and suggested_price=0
group by a.name
) t
group by t.name

转载于:https://www.cnblogs.com/zhanghonjiang2011/archive/2011/12/22/2297924.html