mysql - 如何在 LEFT JOIN 中求和

Question

我有来自两个表的一些数据:

Table prod_schedule
Line      Model      Lot_no      Quantity    Lot_Quantity
FA 01     KD-R330JD  060A        240         1000
FA 01     KD-R330JD  060A        260         1000
FA 02     KD-R330JD  060A        400         1000
FA 02     KD-R330JD  060A        100         1000

Table inspection_report
Line      Model      Lot_no      Merchandise
FA 01     KD-R330JD  060A        200        
FA 01     KD-R330JD  060A        300         
FA 02     KD-R330JD  060A        500         

我想显示如下数据:

Line      Model      Lot_no      Merchandise   Quantity
FA 01     KD-R330JD  060A        500           500
FA 02     KD-R330JD  060A        500           500

我尝试一些查询，例如:

SELECT A.Line, TRIM(A.Model) AS Model,A.Lot_no,B.Quantity,A.Merchandise
FROM inspection_report A
LEFT JOIN prod_schedule B
ON A.Line= B.Line_Name AND CONCAT('%',B.Model_Code,'%') LIKE CONCAT('%',A.Model,'%') 
                        AND A.Lot_no=B.Lot_No_
WHERE A.Model LIKE '%330%' AND A.Lot_no LIKE '%060%'
GROUP BY A.Line,A.Model,A.Lot_no,Merchandise

但我得到的结果如下:

Line    Model     Lot_no    Merchandise Quantity 
FA 01   KD-R330JD   060A    200          240
FA 02   KD-R330JD   060A    300          240

Answer 1

尝试这样。代码

with a as
    (
    select  Line,Model,Lot_no, sum(Merchandise) as Merchandise  from  inspection_report 
    group by Line,Model,Lot_no

    ),
    b as
    (
    select  Line,Model,Lot_no,SUM(Quantity) as Quantity from   prod_schedule 
    group by Line,Model,Lot_no
    )

    select a.Line,a.Model,a.Lot_no,a.Merchandise ,b.Quantity from a,b where a.Line= b.Line