如何使用sql查询显示数据 [英] How to display the data using sql query

问题描述

如何使用sql查询显示数据



我的表名是LoanDetails，我的数据如下所示



UserId，MonthId，YearId，LoanAmount，分期付款

1,12,12012,15000,0

2,12 ，2012,25000,0

3,12,12012,20000,0

1,12 2012 0,1000

2,12， 2012,0,2500

3,12,12012,0,1,500

1,1,20,20,01

2,1 ，2013,0,1500

3,1,2013,0,2000

1,2,2013,0,2000

2， 2,2013,0,2500

3,2,2013,0,2000

1,3,2013,0,1,500

2 ，3,2013,0,2000

3,3,2013,0,2000





can有人告诉我select语句使用以上数据显示如下数据，其中monthid和YearId



如果我给出MOnthId 12和YearId 2012数据如下



UserId，MonthId，Year，InstallmentAmt，PrevLoanAmt，PendingLoanAmt

1 ，12,2012,1000,0,14000

2,12,2012,2500,0,22500

3,12,12012,1500,0,18500





如果我给出MOnthId 1和YearId 2013数据显示如下



UserId，MonthId，Year，Installmentamt，PrevLoanAmt，PendingLoanAmt

1,1,2013,1000,14000,13000

2,1,2013,22500,21000

3,1,2013,2000,18500,16500





如果我给出MOnthId 2和YearId 2013数据显示如下



UserId，MonthId，Year，InstallemtAmt，PrevLoanAmt，PendingLoanAmt

1,2,2013,2000,13000,11000

2,2,2013,2500,21000,18500

3,2,2013,2000,16500,14500





如果我给出MOnthId 3和YearId 2013数据显示如下



UserId，MonthId，Year，分期付款，PrevLoanAmt，PendingLoanAmt

1,3,2013,1500,11000,9500

2,3,2013,2000,18500,16500

3,3,2013,2000,14500,12500





谢谢，

解决方案

你需要编写程序

试试这些

  create   procedure  usp_getLoanDetails 
（
  @ MonthId  [< span class =code-keyword> int ]，
  @ YearId  [ int ] 
）
  as  
  begin  
 选择 
 L.UserId，
 L.MonthId，
 L.YearId，
 L.LoanAmt，
 L.Installment 
  fr om  LoanDetails L 
  WHERE  
 L.MonthId = @ MonthId  AND  L.YearId =  @ YearId  
  end  

您需要编写如下所示的选择查询：

 选择 * 来自 LoanDetails 其中​​ UserId = 1 和 MonthId = 12 和年份= 2012 

如果您的目的已经解决，请将此标记为答案。

谢谢

Hi,
How to display data using sql query

My Table name is LoanDetails and my Data Looks like below

UserId, MonthId, YearId, LoanAmount, Installment
1, 12, 2012, 15000, 0
2, 12, 2012, 25000, 0
3, 12, 2012, 20000, 0
1, 12 2012 0, 1000
2, 12, 2012, 0, 2500
3, 12, 2012, 0, 1500
1, 1, 2013, 0, 1000
2, 1, 2013, 0, 1500
3, 1, 2013, 0, 2000
1, 2, 2013, 0, 2000
2, 2, 2013, 0, 2500
3, 2, 2013, 0, 2000
1, 3, 2013, 0, 1500
2, 3, 2013, 0, 2000
3, 3, 2013, 0, 2000


can anybody tell me select statement to display data like below using above data, where monthid and YearId

If i give MOnthId 12 and YearId 2012 data like below

UserId, MonthId, Year , InstallmentAmt, PrevLoanAmt ,PendingLoanAmt
1 , 12 , 2012 ,1000, 0 , 14000
2 , 12 , 2012 ,2500, 0 , 22500
3 , 12 , 2012 ,1500, 0 , 18500


If i give MOnthId 1 and YearId 2013 data display like below

UserId, MonthId, Year ,Installmentamt, PrevLoanAmt, PendingLoanAmt
1 , 1 , 2013 ,1000, 14000 , 13000
2 , 1 , 2013 ,1500, 22500 , 21000
3 , 1 , 2013 ,2000, 18500 , 16500


If i give MOnthId 2 and YearId 2013 data display like below

UserId, MonthId, Year,InstallemtAmt, PrevLoanAmt, PendingLoanAmt
1 , 2 , 2013 , 2000, 13000 , 11000
2 , 2 , 2013 , 2500, 21000 , 18500
3 , 2 , 2013 , 2000, 16500 , 14500


If i give MOnthId 3 and YearId 2013 data display like below

UserId, MonthId, Year, Installment, PrevLoanAmt, PendingLoanAmt
1 , 3 , 2013 , 1500, 11000 , 9500
2 , 3 , 2013 , 2000, 18500 , 16500
3 , 3 , 2013 , 2000, 14500 , 12500


Thanks,

解决方案

You need to write procedure
try these

create procedure usp_getLoanDetails
(
    @MonthId [int],
    @YearId [int]
)
 as
 begin
	select
	  L.UserId,
	  L.MonthId,   
	  L.YearId, 
	  L.LoanAmt,    
	  L.Installment
        from  LoanDetails L    
	WHERE      
	     L.MonthId =@MonthId AND L.YearId= @YearId
 end

You need to write your select query like this:

Select * from LoanDetails where UserId=1 and MonthId=12 and Year=2012

Please mark this as a answer if your purpose is solved.
Thanks

这篇关于如何使用sql查询显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！