我有 3 張桌子（一個父母和 2 個孩子）如下
- 學生（家長）
StudentID | Name | Age 1 | AA | 23 2 | BB | 25 3 | CC | 27
- 書（兒童1）
BookID | SID | BookName | BookPrice 1 | 1 | ABC | 20 2 | 1 | XYZ | 15 3 | 3 | LMN | 34 4 | 3 | DDD | 90
- 鋼筆（兒童 2）
PenID | SID | PenBrandName | PenPrice 1 | 2 | LML | 20 2 | 1 | PARKER | 15 3 | 2 | CELLO | 34 4 | 3 | LML | 90
我需要加入表格并獲得如下輸出
StudentID | Name | Age | BookNames | TotalBookPrice | PenBrands | TotalPenPrice 1 | AA | 23 | ABC, XYZ | 35 | PARKER | 15 2 | BB | 25 | null | 00 | LML, CELLO | 54 3 | CC | 27 | LMN, DDD | 124 | LML | 90
這是我嘗試過的代碼：
Select s.studentID as "StudentID", s.name as "Name", s.age as "AGE",
LISTAGG(b.bookName, ',') within group (order by b.bookID) as "BookNames",
SUM(b.bookPrice) as "TotalBookPrice",
LISTAGG(p.penBrandName, ',') within group (order by p.penID) as "PenBrands",
SUM(p.penPrice) as "TotalPenPrice"
FROM Student s
LEFT JOIN BOOK b ON b.SID = s.StudentID
LEFT JOIN PEN p ON p.SID = s.StudentID
GROUP BY s.studentID, s.name, s.age
我得到的結果有 Book 和 Pen 的多個值（叉積導致多個值）
StudentID | Name | Age | BookNames | TotalBookPrice | PenBrands | TotalPenPrice
1 | AA | 23 | ABC,ABC,XYZ,XYZ | 35 | PARKER,PARKER | 15
請讓我知道如何解決這個問題。
uj5u.com熱心網友回復：
而不是加入表格并進行聚合，您必須先聚合然后加入您的表格 -
Select s.studentID as "StudentID", s.name as "Name", s.age as "AGE",
"BookNames",
"TotalBookPrice",
"PenBrands",
"TotalPenPrice"
FROM Student s
LEFT JOIN (SELECT SID, LISTAGG(b.bookName, ',') within group (order by b.bookID) as "BookNames",
SUM(b.bookPrice) as "TotalBookPrice"
FROM BOOK
GROUP BY SID) b ON b.SID = s.StudentID
LEFT JOIN (SELECT SID, LISTAGG(p.penBrandName, ',') within group (order by p.penID) as "PenBrands",
SUM(p.penPrice) as "TotalPenPrice"
FROM PEN
GROUP BY SID) p ON p.SID = s.StudentID;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/485852.html
下一篇：在SQL中使用左反連接