方法一:在 GROUP BY 加上(多表的外键) C.ID SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID WHERE C.GRADE = 2010 GROUP BY O.ID,O.NAME,O.ORDERS,C.ID ORDER BY O.ORDERS
方法二:在返回结果数据的 地方加上DISTINCT函数, COUNT(DISTINCT(C.ID)) SELECT O.ID AS ID,O.NAME AS NAME,COUNT(DISTINCT(C.ID)) AS CLASS_NUM FROM T_BASE_ORGANIZATION O LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID WHERE C.GRADE = 2010 GROUP BY O.ID,O.NAME,O.ORDERS ORDER BY O.ORDERS
方法三:在做left join、right join、inner join的时候将 关联的表先DISTINCT(外键) 子查询一遍 SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID LEFT JOIN (SELECT DISTINCT(Z.CLASS_ID) FROM T_SZDW_CLASSMEETING_PLAN Z ) P ON C.ID = P.CLASS_ID WHERE C.GRADE = 2010 GROUP BY O.ID,O.NAME,O.ORDERS ORDER BY O.ORDERS