如何从这两个表中获得剩余的sal [英] How do I get remaining sal from these two tables

问题描述

我有两张桌子



表1带有sal详情



ID ENAME SAL

1 ANU 10000

2 APARNA 20000

3 ANANYA 15000

4 ANUPAMA 40000

5 AMRUTHA 20000



表2预付款详情



ID ADVANCE

1 1000

2 500

5 200

1 2000

2 200



我尝试了什么：



i想要减去提前值在table2从表中的sal



输出：>



ID ENAME remainingin_SAL



1澳大利亚国立大学7000

2 APARNA 19300

3 ANANYA 15000

4 ANUPAMA 40000
5 AMRUTHA 19800

解决方案

我希望我不会为你做功课......



有两件事使这有点棘手，首先你必须构建一个临时查询，它总结进展并加入主表。第二个棘手的问题是，您必须确保在临时查询中表示所有名称，以便它们不会被连接排除。这意味着要考虑NULLS并替换为ZERO预付值。

 drop table #tempadvance 
 
 CREATE TABLE #tempSaldetail（
 [ID] [int] NOT NOT NULL，
 [ENAME] [varchar]（32）NOT NULL，
 [SAL] [INT] NOT NULL 
）
 
 CREATE TABLE #tempAdvance（
 [ID] [int] NOT NULL，
 [ADVANCE] [int] NOT NULL 
）
 GO 
 
插入#tempSaldetail select 1，'ANU '，10000 
插入#tempSaldetail选择2，'APARNA'，20000 
插入#tempSaldetail选择3，'ANANYA'，15000 
插入#tempSaldetail选择4，'ANUPAMA'， 40000 
插入#tempSaldetail选择5，'AMRUTHA'，20000 
 
 INSERT INTO #tempAdvance SELECT 1,1000 
 INSERT INTO #tempAdvance SELECT 2,500 
 INSERT INTO #tempAdvance SELECT 5,200 
 INSERT INTO #tempAdvance SELECT 1,2000 
 INSERT INTO #tempAdvance SELECT 2,200 
 
 
 SELECT s.ID，ENAME， s.Sal  - （adv.totalAdvance）来自#tempSaldetail s 
 join（选择sal.id，ISNULL（sum（advance），0）作为totaladvance来自#tempSaldetail sal 
左外连接#tempAdvance A on 
 sal.id = a.id group by sal.id）as adv 
 adv.id = s.id 

I am having two tables

table 1 with sal details

ID ENAME SAL
1 ANU 10000
2 APARNA 20000
3 ANANYA 15000
4 ANUPAMA 40000
5 AMRUTHA 20000

table 2 with advance details

ID ADVANCE
1 1000
2 500
5 200
1 2000
2 200

What I have tried:

i want to subtract the value of advance in table2 from sal in table

output:>

ID ENAME remainin_SAL

1 ANU 7000
2 APARNA 19300
3 ANANYA 15000
4 ANUPAMA 40000
5 AMRUTHA 19800

解决方案

I hope I'm not doing your homework for you...

There are 2 things that make this a little tricky, first you have to construct an interim query that sums the advances and join to the main table. The second tricky thing is that you have to make sure all names are represented in the interim query so they will not be excluded by the join. This means accounting for NULLS and replacing with a ZERO advance value.

drop table #tempadvance

CREATE TABLE #tempSaldetail(
	[ID] [int] NOT NULL,
	[ENAME] [varchar](32) NOT NULL,
	[SAL] [INT] NOT NULL
) 

CREATE TABLE #tempAdvance(
	[ID] [int] NOT NULL,
	[ADVANCE] [int] NOT NULL
	)
GO

insert into #tempSaldetail select 1, 'ANU', 10000
insert into #tempSaldetail select 2, 'APARNA', 20000
insert into #tempSaldetail select 3, 'ANANYA', 15000
insert into #tempSaldetail select 4, 'ANUPAMA', 40000
insert into #tempSaldetail select 5, 'AMRUTHA', 20000

INSERT INTO #tempAdvance SELECT 1, 1000
INSERT INTO #tempAdvance SELECT 2, 500
INSERT INTO #tempAdvance SELECT 5, 200
INSERT INTO #tempAdvance SELECT 1, 2000
INSERT INTO #tempAdvance SELECT 2, 200


SELECT s.ID, ENAME, s.Sal - (adv.totalAdvance) from #tempSaldetail s
join (select sal.id, ISNULL(sum(advance),0) as totaladvance from  #tempSaldetail sal  
	left outer join #tempAdvance A on 
	sal.id = a.id group by sal.id) as adv
on adv.id = s.id

这篇关于如何从这两个表中获得剩余的sal的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！