如何使用sum方法和where条件在两个表之间编写select查询 [英] How to write the select query between two tables using sum method and where condition

问题描述

我有两个表名和数据，下表给出了



Loans_Advances_Master：

< pre lang =text> 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' 

< br $> 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 below

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

how 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_1

de_1  emp_1    0
de_1   emp_1   5000

de

What 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 LAM

I 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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！