sql - 与 group by (Sum) 的多个联接

Question

当我使用多个 JOIN 时，我希望得到连接表中某些列的总和。

SELECT 
    A.*, 
    SUM(C.purchase_price) AS purcchase_total, 
    SUM(D.sales_price) AS sales_total, 
    B.user_name
FROM
    PROJECT AS A 
LEFT JOIN 
    USER AS B ON A.user_idx = B.user_idx 
LEFT JOIN 
    PURCHASE AS C ON A.project_idx = C.project_idx
LEFT JOIN 
    SALES AS D ON A.project_idx = D.project_idx
GROUP BY 
    ????

标签： sqlsql-serverjoinsum

Answer 1

SELECT A.project_idx,
       a.project_name, 
       A.project_category,
       purcchase_total, 
       sales_total,
       B.user_name
FROM PROJECT AS A 
LEFT JOIN USER AS B ON A.user_idx = B.user_idx 
LEFT JOIN (select project_idx, sum(purchase_price) as purchase_total 
             from PURCHASE group by project_idx ) AS C ON A.project_idx = C.project_idx
LEFT JOIN (select project_idx, sum(sale_price) as sale_total 
             from SALES group by project_idx) AS D ON A.project_idx = D.project_idx

这在 MS-SQL Server 上正常工作。感谢大力水手