mysql count的子查询_使用COUNT进行子查询的慢MYSQL查询

我首先尝试重组，通过供应商预先查询问题的数量并审核ONCE。然后，加入其余的细节。通过使用STRAIGHT_JOIN关键字，它应按显示的顺序进行处理。这将首先预先聚合并使用THAT作为加入供应商和供应商站点的基础。无论如何都不需要外部组，因为它基于供应商ID。但是，对supplier_sites(您的ss.supplier_no)的加入意味着供应商有多个位置。这是否意味着地址和活动状态列来自该表？

问题的加入是否应与特定供应商相关联，是否与相应的网站位置相关联？

此外，由于prequery在supplier_id！='0'上具有WHERE子句，因此不需要下游，因为这将是与其他表的正常连接的基础，从而将它们从结果集中排除。

SELECT STRAIGHT_JOIN

PreAggregate.supplier_id,

PreAggregate.supplier_name,

address1,

address2,

address3,

address4,

suppliertype,

postcode,

contact_name,

PreAggregate.Questions,

IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated,

PreAggregate.Reviewed,

questapproved,

ss.supplier_no AS supplier_no

FROM

(select

s1.Supplier_ID,

s1.Supplier_Name,

SUM( IF( q1.Incomplete = '0', 1, 0 )) Questions,

SUM( IF( q1.Reviewed = '1', 1, 0 )) Reviewed

from

suppliers s1

join supplier_questions q1

ON s1.supplier_id = q1.supplier_id

where

s1.supplier_id != '0'

group by

s1.Supplier_ID

ORDER BY

s1.supplier_name ASC ) PreAggregate

JOIN suppliers c

ON PreAggregate.Supplier_ID = c.Supplier_ID

JOIN supplier_site ss

ON PreAggregate.Supplier_ID = ss.supplier_id

AND ss.Site_ID = '2'

LIMIT 0, 20