如何从sqlserver中的不同表中减去值 [英] How to subtract values from different tables in sqlserver

问题描述

如何从SqlServer中的不同表中减去值



我有两个表dbo.Fee_Payable_to_Students和dbo.Fee_Receipt_Students



dbo.Fee_Payable_to_Students具有值

how to subtract values from different tables in SqlServer

I have two tables dbo.Fee_Payable_to_Students and dbo.Fee_Receipt_Students

dbo.Fee_Payable_to_Students has values

S_Adm_No | cls_SecId | FHeadId| Apr | May | Jun | Jul | Aug | Sep
1001     |     1     |     1  | 500 | 500 | 500 | 500 | 500 | 500
1001     |     1     |     2  | 400 | 400 | 400 | 400 | 400 | 400

dbo.Fee_Receipt_学生有价值

dbo.Fee_Receipt_Students has values

AutoGenNo|S_Adm_No | cls_SecId | FHeadId| Apr | May | Jun | Jul | Aug | Sep
1        |1001     |     1     |     1  | 100 | 100 | 100 | 100 | 100 | 100
2        |1001     |     1     |     1  | 100 | 100 | 100 | 100 | 100 | 100
3        |1001     |     1     |     1  | 100 | 100 | 100 | 100 | 100 | 100
4        |1001     |     1     |     1  | 100 | 100 | 100 | 100 | 100 | 100
5        |1001     |     1     |     2  | 100 | 100 | 100 | 100 | 100 | 100
6        |1001     |     1     |     2  | 100 | 100 | 100 | 100 | 100 | 100

我想从Fee_Payable_to_Students表减去价值

值应显示

I want subtract value from Fee_Payable_to_Students table
the value should show

dbo.Fee_Payable_to_Students has values 
S_Adm_No | cls_SecId | Apr | May | Jun | Jul | Aug | Sep
1001     |     1     | 300 | 300 | 300 | 300 | 300 | 300

推荐答案

这是你的作业，所以我不会为你写这个查询。

但是它非常简单：在每个表上使用GROUP BY来汇总信息，然后使用JOIN来组合摘要，然后选择每个月的结果为

This is your homework, so I'll not write the query for you.
But it's pretty simple: USE GROUP BY on each table to summarise the information, then use a JOIN to combine the summaries, and then SELECT the result for each month as

SUM(g1.Apr) - SUM(g2.Apr) AS Apr

其中g1和g2是GROUPed数据。

如果听起来太复杂，首先依次选择每个GROUP并返回汇总值（这非常简单），你应该开始明白我的意思。

Where g1 and g2 are the GROUPed data.
If it sounds too complex, start by SELECTing each GROUP in turn and return the summary values (which is pretty trivial) and you should start to see what I mean.

试试这个：

Try this:

DECLARE @FPS AS TABLE(S_Adm_No INT, cls_SecId INT, FHeadId INT, Apr INT, May INT, Jun INT, Jul INT, Aug INT, Sep INT)
INSERT INTO @FPS (S_Adm_No, cls_SecId, FHeadId, Apr, May, Jun, Jul, Aug, Sep)
VALUES(1001, 1, 1, 500, 500, 500, 500, 500, 500),
(1001, 1, 2, 400, 400, 400, 400, 400, 400)


DECLARE @FRS AS TABLE(AutoGenNo INT IDENTITY(1,1), S_Adm_No INT, cls_SecId INT, FHeadId INT, Apr INT, May INT, Jun INT, Jul INT, Aug INT, Sep INT)
INSERT INTO @FRS (S_Adm_No, cls_SecId, FHeadId, Apr, May, Jun, Jul, Aug, Sep)
VALUES(1001, 1, 1, 100, 100, 100, 100, 100, 100),
(1001, 1, 1, 100, 100, 100, 100, 100, 100),
(1001, 1, 1, 100, 100, 100, 100, 100, 100),
(1001, 1, 1, 100, 100, 100, 100, 100, 100),
(1001, 1, 2, 100, 100, 100, 100, 100, 100),
(1001, 1, 2, 100, 100, 100, 100, 100, 100)

SELECT FPS.S_Adm_no, FPS.cls_SecId, COALESCE(FPS.AprA,0) - COALESCE(FRS.AprB,0) AS Apr, COALESCE(FPS.MayA,0) - COALESCE(FRS.MayB,0) AS May, 
		COALESCE(FPS.JunA,0) - COALESCE(FRS.JunB,0) AS Jun, COALESCE(FPS.JulA,0) - COALESCE(FRS.JulB,0) AS Jul
FROM (
	(
		SELECT S_Adm_no, cls_SecId, SUM(Apr) AprA, SUM(May) MayA, SUM(Jun) JunA, SUM(Jul) JulA, SUM(Aug) AugA, SUM(Sep) SepA
		FROM @FPS
		GROUP BY S_Adm_no, cls_SecId
	) AS FPS
	LEFT JOIN
	(
		SELECT S_Adm_no, cls_SecId, SUM(Apr) AprB, SUM(May) MayB, SUM(Jun) JunB, SUM(Jul) JulB, SUM(Aug) AugB, SUM(Sep) SepB
		FROM @FRS
		GROUP BY S_Adm_no, cls_SecId
	) AS FRS ON FPS.S_Adm_no = FRS.S_Adm_no AND FPS.cls_SecId = FRS.cls_SecId
) 

注意：随意改进它！

Note: Feel free to improve it!

这篇关于如何从sqlserver中的不同表中减去值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！