这个SQL,只能从索引上来做优化,还有那个IN语句。请楼主给有每个字段前加别名限制,并给出各张表的数据量情况。 SELECT DISTINCT a.Com_ID , a.Com_Name , a.Com_Delegate , a.Com_ComCode , a.Pro_Receipts , Com_IsCetifYezz , Com_IsCetifYzz , Com_IsCetifWghcd , Com_IsCetifghcp , --请说明这个字段属于哪张表.比如 a.Com_IsCetifghcp? Com_IsCetifCk , Com_IsCetifZm , Com_IsCetifHaccp , Com_IsCetifIso , Com_IsCetifGreen , Com_ShowOrder , s1.V_Text AS Com_QYXZ , --企业性质 s2.V_Text AS Com_AuditorStatus --核查状态 FROM Wat_ComInfo a LEFT JOIN sys_FieldValue s1 ON s1.V_Code = a.Com_QYXZ_Dic AND s1.V_F_Key = 'ComQYXZKey' --企业性质 LEFT JOIN sys_FieldValue s2 ON s2.V_Code = a.Com_AuditorStatus_Dic AND s2.V_F_Key = 'ComAuditorStatusKey' --核查状态 LEFT JOIN Wat_FarmCertificate f ON f.Com_ID = a.Com_ID --养殖证,请说出表中的数据量? LEFT JOIN Wat_ProComRelation c ON c.Com_ID = a.Com_ID --产品与企业关系表 LEFT JOIN Wat_ProductInfo d ON d.Pro_ID = c.Pro_ID WHERE Com_NewOldStatus_Dic = '1' AND Com_AreaCode IN --请说明这个字段属于哪张表.比如 a.Com_AreaCode? ( SELECT f.AreaCode FROM Wat_OrgRole f JOIN Wat_OrgInfo e ON e.Org_ID = f.Org_ID JOIN org_user g ON e.Org_Name = g.U_UnitName WHERE g.UserID = '1' )
SELECT DISTINCT
a.Com_ID ,
a.Com_Name ,
a.Com_Delegate ,
a.Com_ComCode ,
a.Pro_Receipts ,
Com_IsCetifYezz ,
Com_IsCetifYzz ,
Com_IsCetifWghcd ,
Com_IsCetifghcp , --请说明这个字段属于哪张表.比如 a.Com_IsCetifghcp?
Com_IsCetifCk ,
Com_IsCetifZm ,
Com_IsCetifHaccp ,
Com_IsCetifIso ,
Com_IsCetifGreen ,
Com_ShowOrder ,
s1.V_Text AS Com_QYXZ , --企业性质
s2.V_Text AS Com_AuditorStatus --核查状态
FROM Wat_ComInfo a
LEFT JOIN sys_FieldValue s1
ON s1.V_Code = a.Com_QYXZ_Dic
AND s1.V_F_Key = 'ComQYXZKey' --企业性质
LEFT JOIN sys_FieldValue s2
ON s2.V_Code = a.Com_AuditorStatus_Dic
AND s2.V_F_Key = 'ComAuditorStatusKey' --核查状态
LEFT JOIN Wat_FarmCertificate f
ON f.Com_ID = a.Com_ID --养殖证,请说出表中的数据量?
LEFT JOIN Wat_ProComRelation c
ON c.Com_ID = a.Com_ID --产品与企业关系表
LEFT JOIN Wat_ProductInfo d
ON d.Pro_ID = c.Pro_ID
WHERE Com_NewOldStatus_Dic = '1'
AND Com_AreaCode IN --请说明这个字段属于哪张表.比如 a.Com_AreaCode?
(
SELECT f.AreaCode
FROM Wat_OrgRole f
JOIN Wat_OrgInfo e
ON e.Org_ID = f.Org_ID
JOIN org_user g
ON e.Org_Name = g.U_UnitName
WHERE g.UserID = '1'
)
Wat_ComInfo 这个主表的数据有4万多,子表Wat_FarmCertificate有5万多,其他表的数据不多。