mysql - 加入查询返回错误的输出

Question

我的数据库中有 4 个表

第一张表:学生

id  Name   roll   wheree    final
1   vivek  22222  leave     65000
2   abc    33333  admission 50000

第二张表:费用

id  amount   roll   refund 
1   9250     22222  no
2   5000     22222  no
2   5000     33333  no
2   7000     22222  yes

第三张表:pdc

id  amount   roll   refund   statuss
1   9250     22222  no       recived
2   9250     22222  no       cleared
2   6000     22222  yes      recived

第四张表:折扣

id    amount       roll     
1     5000         22222    
2     6500         22222    
2     6000         33333    

我在努力奔跑

select student.id,student.roll,student.name as
stu,student.final,ifnull(sum(gst_amount),0) as other,
(student.final-ifnull(sum(gst_amount),0)) as fee_to_paid,
ifnull(sum(fee.amount),0) as fee_deposit,(student.final-ifnull(sum(gst_amount),0)
-ifnull(sum(fee.amount),0)
-ifnull(sum(pdc.amount),0)) as fee_remain,ifnull(sum(pdc.amount),0) as pdc,count(fee.amount) 
as tot_ins from student left join fee on fee.roll=student.roll
 left join discount on discount.roll=student.roll left join pdc
 on pdc.roll=student.roll where
  wheree!='inquiry' where roll='22222'  GROUP by student.roll

我想要这个输出。

id roll   final  other  fee_to_paid fee_deposit pdc    fee_remain   tot_ins
1  22222  65000  11000  54000       7250        9250   37500        2             

我想从 pdc 中减去退款值为 y 的费用金额

Answer 1

我已阅读您的问题并观察您的表数据和查询。您没有为 fee 和 pdc 表 指定 Where 子句用于 refunds value yes。所以，试试下面的查询，我已经纠正了......

select  student.id
    ,student.roll
    ,student.name as stu
    ,student.final
    ,ifnull(sum(gst_amount),0) as other
    ,(student.final-ifnull(sum(gst_amount),0)) as fee_to_paid
    ,ifnull(sum(fee.amount),0) as fee_deposit,(student.final-ifnull(sum(gst_amount),0)-ifnull(sum(fee.amount),0)-ifnull(sum(pdc.amount),0)) as fee_remain
    ,ifnull(sum(pdc.amount),0) as pdc,count(fee.amount) as tot_ins 
    from student left join fee on fee.roll=student.roll AND fee.refund='yes'
                     left join discount on discount.roll=student.roll 
                 left join pdc on pdc.roll=student.roll AND pdc.refund='yes' AND  wheree!='inquiry' 
where student.roll='22222'  GROUP by student.roll