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

ゝ一世哀愁。 2022-02-23 16:52 258阅读 0赞

## 先放一条 重复数据 的脏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。**