在加入两个表时遇到问题 [英] Facing Problem in Joining two tables

问题描述

实际上我在加入两张桌子时遇到问题并获得正确的信息，例如我有两张桌子（Fee_Quotation，Paid_Details）



table 1

//费用报价表（入学时学生将承担此金额。下面有一个学生费用报价详情1001）

Actually i am facing problem in joining of two tables and getting the right information for example i am having two tables(Fee_Quotation,Paid_Details)

table 1
//Fee_Quotation table(while admission student will commit for this amount.here below there is one student fee_quotation details 1001)

Student_ID|Fee_Type         |Fee_Amount|Payment_Type   |Commited_on
1001      |Registration_fee |100       |Annualy        |2013-mar-8
1001      |Tution_fee       |1000      |Monthly        |2013-mar-8
1001      |Books            |1000      |Annualy        |2013-mar-8

表2

// Paid_details表（这里学生将根据月份支付费用）

table 2
//Paid_details table(here student will pay fee according to month)

Student_ID|Fee_Type         |Fee_Amount|Payment_Type |Month  |Paid_on
1001      |Registration_fee |100       |Annualy      |mar    |2013-mar-11
1001      |Tution_fee       |500       |Monthly      |mar    |2013-mar-12
1001      |Tution_fee       |500       |Monthly      |mar    |2013-mar-13
1001      |Books            |500       |Annualy      |mar    |2013-mar-11
1001      |Books            |300       |Annualy      |mar    |2013-mar-12
1001      |Tution_fee       |500       |Monthly      |apr    |2013-apr-12
1001      |Tution_fee       |400       |Monthly      |apr    |2013-apr-13

现在我将传递三个参数，例如Student_ID = @ Student_ID，Payment_type = @ Payment_Type，Month = @ Month

ex： - Student_ID = 1001，Payment_Type =''Monthly''，月=''apr''

然后我想要如下表格

now i will pass three parameters like Student_ID=@Student_ID,Payment_type=@Payment_Type,Month=@Month
ex:- Student_ID=1001,Payment_Type=''Monthly'',Month=''apr''
then i want the table like below

Student_ID|Fee_Type   |Fee_Amount|LastMoDue|CurrentMoDue|TotalDue
1001      |Tution_Fee |1000      |0.00     |100         |100

如果我将传递两个参数，例如

Student_ID = @ Student_ID，Payment_type = @ Payment_Type

ex： - Student_ID = 1001，Payment_Type =''Annualy''

然后我想要如下表格

if i will pass two parameters like
Student_ID=@Student_ID,Payment_type=@Payment_Type
ex:- Student_ID=1001,Payment_Type=''Annualy''
then i want the table like below

Student_ID|Fee_Type   |Fee_Amount|LastMoDue|CurrentMoDue|TotalDue
1001      |Regist_Fee |100       |0.00     |100         |100
1001      |Books      |1000      |0.00     |1000        |1000

//这种类型的表我们可以得到ha先生.. ..i意味着有可能ha先生





[MOVED]来自评论[/ MOVED]

//this type of table we can get ha sir....i mean it is possible ha sir


[MOVED] from comment [/MOVED]

 select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount from Student_Fee_Quotations q join (select d.Student_ID,d.Fee_Type,sum(d.amount) as paid
from FeePaid_Details d
where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='jan' group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' join (select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='feb' group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'

推荐答案

您可以通过实现这一目标。存储过程 [ ^ ]像这样：

You can achieve that with stored procedure[^] like this:

CREATE PROCEDURE GetStudentFees()
    @Student_ID INT,
    @Payment_Type NVARCHAR(30),
    @Month NVARCHAR(30)
AS
BEGIN

    IF (ISNULL(@Month,'')='')
        BEGIN
            --your code to get data for 2 passed input parameters
            SELECT Student_ID, Fee_Type, SUM(Fee_Amount) AS TotalDue
            FROM YourTable
            WHERE [Student_ID] = @Studnet_ID AND [Payment_Type] = @Payment_Type
            GROUP BY [Student_ID], [Fee_Type]
        END
    ELSE
        BEGIN
            --your code to get data for 3 passed input parameters
            SELECT Student_ID, Fee_Type, SUM(Fee_Amount) AS TotalDue
            FROM YourTable
            WHERE [Student_ID] = @Studnet_ID AND [Payment_Type] = @Payment_Type AND [Month] = @Month
            GROUP BY [Student_ID], [Fee_Type]
        END
END

我不知道你的意思 LastMoDue 和 CurrentMoDue ，所以......我无法帮助你。

I don''t know what you mean LastMoDue And CurrentMoDue, so... i can''t help you.

这篇关于在加入两个表时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！