帮我解决SQL查询问题 [英] Help me on SQL Query
问题描述
大家好,
我有2张桌子EmpLoans和EmpPayments,桌子数据如下
EmpLoans
UserId LoanAmt
229 15000
229 3000
229 12500
EmpPayments
UserId Date LoanInstallment   RemainingAmt
229 1/31/2013 1500   29000
229 2/28/2013 2500   26500
229 3/31/2013 3500   23000
229 4/30/2013 3000   20000
我希望使用sql查询下面的数据
UserId 日期 Sum(LoanAmt) LoanInstallment RemainingAmt
229 1/31/2013 30500 1500 29000
229 2/28/2013年   30500   2500   26500
229 3/31/2013年   30500   3500   23000
229 4/30/2013年   30500   3000   20000
任何人都可以帮我查询上面的查询。
谢谢,
填充表格
创建 table emploans(userid int ,Loanamount decimal ( 18 , 2 ));
插入 进入雇员(userid,loanamount)值( 229 , 15000 );
插入 进入雇员(userid,loanamount)值( 229 , 3000 );
插入 进入雇员(userid,loanamount)值( 229 , 12500 );
创建 表 empPayments(userid int ,[日期] 日期,LoanInstallment 十进制( 18 , 2 ),RemainingAmt Decimal ( 18 , 2 ));
插入 进入 empPayments(userid, date ,LoanInstallment,RemainingAmt)值( 229 , ' 2013-01-31', 1500 , 29000 跨度>);
插入 进入 empPayments(userid, date ,LoanInstallment,RemainingAmt)值( 229 , ' 2013-02-28', 2500 , 26500 跨度>);
插入 进入 empPayments(userid, date ,LoanInstallment,RemainingAmt)值( 229 , ' 2013-03-31', 3500 , 23000 跨度>);
插入 进入 empPayments(userid, date ,LoanInstallment,RemainingAmt)值( 229 , ' 2013-04-30', 3000 , 20000 );
执行查询
选择用户ID,转换( varchar ( 10 ),[ date ], 101 ) as 日期,
( select sum(loanamount)来自从员t2 其中 t2.userid = t1.userid) as [SUM 贷款],
LoanInstallment,remainingamt 来自 emppayments t1 订单 按用户ID,[日期 ]
结果
用户名日期贷款贷款余额余额
229 01/31/2013 30500.00 1500.00 29000.00
229 02/28/2013 30500.00 2500.00 26500.00
229 03/31/2013 30500.00 3500.00 23000.00
229 04/30/2013 30500.00 3000.00 20000.00
Hi all,
I have the 2 tables EmpLoans and EmpPayments, tables Data like below
EmpLoans
UserId LoanAmt
229 15000
229 3000
229 12500
EmpPayments
UserId Date LoanInstallment RemainingAmt
229 1/31/2013 1500 29000
229 2/28/2013 2500 26500
229 3/31/2013 3500 23000
229 4/30/2013 3000 20000
I want the data like below using sql query
UserId Date Sum(LoanAmt) LoanInstallment RemainingAmt
229 1/31/2013 30500 1500 29000
229 2/28/2013 30500 2500 26500
229 3/31/2013 30500 3500 23000
229 4/30/2013 30500 3000 20000
Can anybody help me the query display like above.
Thanks,
Populate the tables
create table emploans (userid int, Loanamount decimal(18,2)); insert into emploans(userid,loanamount) values(229,15000); insert into emploans(userid,loanamount) values(229,3000); insert into emploans(userid,loanamount) values(229,12500); create table empPayments(userid int,[Date] Date, LoanInstallment Decimal(18,2),RemainingAmt Decimal(18,2)); insert into empPayments (userid,date,LoanInstallment,RemainingAmt) values(229,'2013-01-31',1500,29000); insert into empPayments (userid,date,LoanInstallment,RemainingAmt) values(229,'2013-02-28',2500,26500); insert into empPayments (userid,date,LoanInstallment,RemainingAmt) values(229,'2013-03-31',3500,23000); insert into empPayments (userid,date,LoanInstallment,RemainingAmt) values(229,'2013-04-30',3000,20000);
Execute the Query
select userid,convert(varchar(10),[date],101) as Date, (select sum(loanamount) from emploans t2 where t2.userid=t1.userid) as [SUM Of Loans], LoanInstallment,remainingamt from emppayments t1 order by userid,[date]
Results
userid Date SUM Of Loans LoanInstallment remainingamt 229 01/31/2013 30500.00 1500.00 29000.00 229 02/28/2013 30500.00 2500.00 26500.00 229 03/31/2013 30500.00 3500.00 23000.00 229 04/30/2013 30500.00 3000.00 20000.00
这篇关于帮我解决SQL查询问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!