left join、right join、inner join关联查询时出现重复数据

大家好，欢迎来到IT知识分享网。

先放一条 重复数据 的脏SQL

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
ORDER BY O.ORDERS

这表结构是：
T_BASE_ORGANIZATION 学院表
T_XSXX_CLASSINFORMATION 班级表
T_SZDW_CLASSMEETING_PLAN 班会表
一个学院 多个班级
一个班级 多个班会
总之引发查询出重复数据就是因为，表之间关联的关系不是一对一的而是一对多，从而导致数据重复。
解决方式

方法一：在 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

不推荐 使用方法一
ORDER BY 函数 影响范围广。
推荐使用方法一
子查询 作用范围小，不影响整体SQL。