多個左連接獲取重複數據

我有這個龐大的查詢重複的數據。

SELECT        t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2, 
             t2.exam AS agricExam3, t2.result_total AS agricTotal, 
             t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM          
             result_summary AS t1 
             LEFT  JOIN
                    agricultural_science AS t2 
                    ON t1.student_id = t2.student_id
WHERE        (    t1.student_id     = 'GCU/16/10414' 
             AND t1.result_term    = '1st' 
             AND t1.result_session = '2016/2017' 
             AND t1.result_level   = 'JSS' 
             AND t1.result_class   = '2')

表結構

農業科學表的結構如下：

|id|student_id|result_session|result_term|result_arm|
result_class|result_level|1st_test|2nd_test|exam|result_total|
result_grade|result_remark|

Table的表結構Result_Summary如下所示：

|id|student_id|result_score|result_average|result_term|
result_session|result_level|result_class|result_arm

符合條件的數據僅存在於某些表中，而另一些表包含兩行，每行具有相同Student_ID但不同的Session. 執行查詢時，我會得到大量重複，甚至是與2016/2017 session.

請問，我該如何解決這個問題？

您僅在“student_id”上加入，當您需要加入時，至少，也，但result_session很可能所有的，，，，，，。result_term``result_session``result_level``result_class``result_arm

嘗試：

SELECT        
   t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2, 
   t2.exam AS agricExam3, t2.result_total AS agricTotal, 
   t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM            
   result_summary AS t1 
   LEFT JOIN agricultural_science AS t2 
       ON t1.student_id = t2.student_id
          /* This is what I guess you're missing */
          AND t1.result_session = t2.result_session 
WHERE        
   t1.student_id = 'GCU/16/10414' 
   AND t1.result_term = '1st' 
   AND t1.result_session = '2016/2017' 
   AND t1.result_level = 'JSS' 
   AND t1.result_class = '2'

要麼

SELECT        
   t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2, 
   t2.exam AS agricExam3, t2.result_total AS agricTotal, 
   t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM            
   result_summary AS t1 
   LEFT JOIN agricultural_science AS t2 
       ON t1.student_id = t2.student_id
          /* Or most probably you're missing all of this */
          AND t1.result_session = t2.result_session
          AND t1.result_term = t2.result_term
          AND t1.result_level = t2.result_level
          AND t1.result_arm = t2.result_arm
WHERE        
   t1.student_id = 'GCU/16/10414' 
   AND t1.result_term = '1st' 
   AND t1.result_session = '2016/2017' 
   AND t1.result_level = 'JSS' 
   AND t1.result_class = '2'

---

作為旁注：數據庫的這種資料結構可以很容易地改進……

---

我的原始試用版（在與 mySQL 不同的數據庫上）

WITH agricultural_science(id, student_id, result_session,
   result_term, result_arm, result_class, result_level,
   "1st_test", "2nd_test", exam, result_total, result_grade, result_remark)
AS
(
VALUES
(1, 'GCU/16/10414', '2016/2017', 
   '1st', 'arm', '2', 'JSS', 
   93, 92, 'exam', 'result_total', 'result_grade', 'result_remark'), 
(1, 'GCU/16/10414', '2015/2016', 
   '1st', 'arm', '2', 'JSS', 
    99, 23, 'exam', 'result_total', 'result_grade', 'result_remark')

)
, result_summary (id, student_id, result_score, result_average,
   result_term, result_session, result_level, result_class, result_arm
) AS
(
VALUES
(1, 'GCU/16/10414', 99.1, 90.1, 
   '1st', '2016/2017', 'JSS', '2', 'arm')
)

SELECT        
   t2."1st_test" AS agric1, t2."2nd_test" AS agric2, 
   t2.exam AS agricExam3, t2.result_total AS agricTotal, 
   t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM            
   result_summary AS t1 
   LEFT JOIN agricultural_science AS t2 
       ON t1.student_id = t2.student_id
          AND t1.result_session = t2.result_session
          AND t1.result_term = t2.result_term
          AND t1.result_level = t2.result_level
          AND t1.result_arm = t2.result_arm
WHERE        
   t1.student_id = 'GCU/16/10414' 
   AND t1.result_term = '1st' 
   AND t1.result_session = '2016/2017' 
   AND t1.result_level = 'JSS' 
   AND t1.result_class = '2' ;

引用自：https://dba.stackexchange.com/questions/159657