如何在存储过程中使用多个 select sum() 查询 [英] How to use the multiple select sum() queries in a stored procedure

问题描述

我有一个存储过程 challan 正在为费用 challan 工作.

I have a stored procedure challan which is working for fee challan.

但是现在我想展示通过四个查询的帮助收到的会费.

But now I want to show the dues which is received by the help the of four queries.

我想在我的 challan 存储过程中添加这两个:

And I want to add to add that two in my challan stored procedure:

create proc [dbo].[challan]
    @sessionid int,
    @month nvarchar(20)
as
    select distinct 
        student.Student_id as [A/c #], student.Student_Name, 
        parent.father_name, class.class_numeric, invoice.Fee_description, 
        invoice.Amount, invoice.issue_date, invoice.month 
    from 
        student
    join 
        parent on student.father_nic = parent.father_nic
    join 
        allocate_class on student.Student_id = allocate_class.Student_id
    join 
        class on class.class_id = allocate_class.class_id
    join 
        session on allocate_class.session_id = session.session_id
    join 
        invoice on student.Student_id = invoice.Student_id
    where 
        session.session_id = @sessionid 
        and student.status = 'active' 
        and invoice.month = @month
    order by 
        class.class_numeric asc

此查询用于收取当月费用，该费用将从会费中扣除，因为它已在challan 中显示:

This query is used to collect the current month fee which will be subtracted from the dues because it has already shown in the challan:

SELECT 
    SUM(invoice.Amount) 
FROM 
    invoice 
WHERE 
    invoice.month = 'November-2019' 
    AND invoice.Student_id = '115' 

现在我运行另外两个，用于形成发票表中所有 challan 的学生总和，我必须从中减去当月费用

Now I run two another which is used form the sum of the student of all challan in the invoice table from which i have to minus the current month fee

SELECT SUM(invoice.Amount) 
FROM invoice 
WHERE invoice.Student_id = '115

这用于汇总收据表中学生的所有收到的费用:

This is used to sum all the received fee of a student in receipt table:

SELECT SUM(Recipt.Paid_amount) 
FROM Recipt 
WHERE Recipt.Student_id = '115'

现在的问题是减去上面1)和2)查询中的3)查询，然后放入challan存储过程的最后一个.

Now the problem is to minus the 3) query from above 1) and two) query and then put in the last of challan stored procedure.

推荐答案

你可以使用 CASE 条件来达到同样的目的

You can use CASE condition for achieving the same

    select 
    student.Student_id as [A/c #], student.Student_Name, parent.father_name,class.class_numeric, invoice.Fee_description, invoice.Amount, invoice.          issue_date, invoice.month ,
    SUM(CASE WHEN invoice.month = 'November-2019' AND invoice.Student_id = '115' THEN invoice.Amount ELSE 0 END)
from student
join parent on student.father_nic = parent.father_nic
join allocate_class on student.Student_id = allocate_class.Student_id
join class on class.class_id = allocate_class.class_id
join session on allocate_class.session_id = session.session_id
join invoice on student.Student_id = invoice.Student_id
where session.session_id=  @sessionid AND student.status = 'active' AND invoice.month = @month
order by class.class_numeric asc

这篇关于如何在存储过程中使用多个 select sum() 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！