如何使用sum方法和where条件在两个表之间编写select查询

本文介绍了如何使用sum方法和where条件在两个表之间编写select查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有两个表名和数据，下表给出了 Loans_Advances_Master： Dept_id emp_Id loan_approved_amt loan_adv_no ----------------------------------- ------------------- De_1 emp_1 10000 LA_1 de_1 emp_1 10000 LA_2 loans_deduction_details： dept_Id EMp_Id Loan_deduction_amt Loan_adv_no --------- ---------------------------------------- de_1 emp_1 5000 LA_1 de_1 emp_1 5000 LA_1 de_1 Emp_1 5000 LA_2 如何为以上两个表写入选择查询以进行待处理的laon ie loan_approved_amt for loan_adv_no是LA_1 equl到loan（loan_deduction_amt）来自loan_deduction_details表单Loan_adv_no然后显示0其他明智的显示金额需要他应支付的金额.. 如何获得 EMp_1的输出 de_1 emp_1 0 de_1 emp_1 5000 de 我的尝试： 选择 Dept_id，Emp_id，Loan_Adv_No，（（选择 loan_adv_no，sum（Loan_Approved_Amont）来自 [dt_agrovet]。[dbo]。[Loans_Advances_Master] group by Loan_Adv_No） - （ 选择 sum（loan_deduction_amount）来自 loans_deduction_details group by emp_id，Loan_Adv_No）） as loanamount from [dt_agrovet]。[dbo]。[Loans_Advances_Master] where emp_Id = ' emp_1' b $ b 可以请任何人给我这个解决方案... 谢谢... 解决方案 SELECT LAM.Dept_id ，LAM.emp_Id ，（LAM.loan_adv_no-（ SELECT SUM（LDD.Loan_deduction_amt） FROM loans_deduction_details AS LDD WHERE LDD.Loan_adv_no = LAM.loan_adv_no AND LDD.Emp_Id = LAM.Emp_Id）） AS BalanceAmount FROM Loans_Advances_Master AS LAM 我做了一些更改在表结构中重新使查询更清洁。我从表loans_deduction_details中删除了列emp_id，dept_id和loan_adv_no，并添加了一列来保存好的Loans_Advances_Master行的id作为外键。 这是我的小提琴的样子。 创建 table LAM（id int ，depId varchar （ 20 ），empId varchar （ 20 ），loanId varchar （ 20 ），loanAmt int ）; 创建 表 LDD（id int ，lamId int ，loadDAmt int ）; 插入 进入 LAM 值（ 1 ，' De_1'，' emp_1'，' LA_1'， 10000 ），（ 2 ，' De_1'，' emp_1'，' LA_2'， 10000 ）; 插入 到 LDD 值 （ 1 ， 1 ， 5000 ）， （ 2 ， 1 ， 5000 ）， （ 3 ， 2 ， 5000 ）; 现在表已初始化我只需要查询它们。 选择 temp.depID，temp.empID，temp.loanId，temp.loadAmt 来自（ 选择 LAM.depID as depID， LAM .empID as empID， LAM.loanId as loanId， LAM.loanAmt - SUM（LDD.loadDAmt） AS loadAmt FROM LDD INNER JOIN LAM ON LDD.lamId = LAM.Id GROUP BY LDD.lamId ）temp; 你也可以查看小提琴这里 [ ^ ]。 I have two tables names and data in that tables are given belowLoans_Advances_Master:Dept_id emp_Id loan_approved_amt loan_adv_no------------------------------------------------------De_1 emp_1 10000 LA_1de_1 emp_1 10000 LA_2loans_deduction_details:dept_Id EMp_Id Loan_deduction_amt Loan_adv_no-------------------------------------------------de_1 emp_1 5000 LA_1de_1 emp_1 5000 LA_1de_1 Emp_1 5000 LA_2how to write select query for above two tables for pending laon i.e loan_approved_amt for loan_adv_no is LA_1 equl to sum(loan_deduction_amt) from loans_deduction_details form Loan_adv_no Then show 0 other wise show amount required amount should he pay..How to get the output like This for EMp_1de_1 emp_1 0de_1 emp_1 5000deWhat I have tried:select Dept_id, Emp_id, Loan_Adv_No,((select loan_adv_no,sum(Loan_Approved_Amont) from [dt_agrovet].[dbo].[Loans_Advances_Master] group by Loan_Adv_No)- (select sum(loan_deduction_amount) from loans_deduction_details group by emp_id,Loan_Adv_No) ) as loanamount from [dt_agrovet].[dbo].[Loans_Advances_Master] where emp_Id='emp_1'can please any one give me the solution for this ...Thank you ... 解决方案 SELECT LAM.Dept_id ,LAM.emp_Id ,(LAM.loan_adv_no-( SELECT SUM(LDD.Loan_deduction_amt) FROM loans_deduction_details AS LDD WHERE LDD.Loan_adv_no=LAM.loan_adv_no AND LDD.Emp_Id=LAM.Emp_Id) ) AS BalanceAmount FROM Loans_Advances_Master AS LAMI made some changes in the table structure to make the query cleaner. I removed the columns "emp_id", "dept_id", and "loan_adv_no" from the table "loans_deduction_details" and added a column to hold the id of the good row of "Loans_Advances_Master" to act as a foreign key.Here is my what my fiddle looks like.create table LAM (id int, depId varchar(20), empId varchar(20), loanId varchar(20), loanAmt int);create table LDD(id int, lamId int, loadDAmt int);insert into LAM values (1,'De_1','emp_1', 'LA_1', 10000), (2,'De_1','emp_1', 'LA_2', 10000);insert into LDD values (1,1,5000),(2,1,5000),(3,2,5000);Now that the tables are initialized I just need to query them.select temp.depID, temp.empID, temp.loanId, temp.loadAmt from ( select LAM.depID as depID, LAM.empID as empID, LAM.loanId as loanId, LAM.loanAmt - SUM(LDD.loadDAmt) AS loadAmt FROM LDD INNER JOIN LAM ON LDD.lamId = LAM.Id GROUP BY LDD.lamId ) temp;You can also check the fiddle here[^]. 这篇关于如何使用sum方法和where条件在两个表之间编写select查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！