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

乐哈哈yoyo

先放一条 重复数据 的脏SQL

1. SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
   
2. LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
   
3. LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID
   
4. WHERE C.GRADE = 2010
   
5. GROUP BY O.ID,O.NAME,O.ORDERS
   
6. ORDER BY O.ORDERS
   

复制代码

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

1. 方法一：在 GROUP BY 加上（多表的外键） C.ID
   
2. SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
   
3. LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
   
4. LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID
   
5. WHERE C.GRADE = 2010
   
6. GROUP BY O.ID,O.NAME,O.ORDERS,C.ID
   
7. ORDER BY O.ORDERS
   
8. 
   
9.    
   
10. 
    
11. 方法二：在返回结果数据的 地方加上DISTINCT函数, COUNT(DISTINCT(C.ID))
    
12. SELECT O.ID AS ID,O.NAME AS NAME,COUNT(DISTINCT(C.ID)) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
    
13. LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
    
14. LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID
    
15. WHERE C.GRADE = 2010
    
16. GROUP BY O.ID,O.NAME,O.ORDERS
    
17. ORDER BY O.ORDERS
    
18. 
    
19. 
    
20. 方法三：在做left join、right join、inner join的时候将 关联的表先DISTINCT(外键) 子查询一遍   
    
21. SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
    
22. LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
    
23. LEFT JOIN (SELECT DISTINCT(Z.CLASS_ID) FROM T_SZDW_CLASSMEETING_PLAN Z ) P ON C.ID = P.CLASS_ID
    
24. WHERE C.GRADE = 2010
    
25. GROUP BY O.ID,O.NAME,O.ORDERS
    
26. ORDER BY O.ORDERS
    

复制代码

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