如何根据生效日期计算2个表中的总计 [英] How do I calcuate totals from 2 tables based on effective dates

问题描述

我是SQL的新手。我正在构建一个数据库，但很难确定表C中的项目总数来自2个表格A& B根据生效日期在下面。

表A

I am a very new to SQL. I am building a DB, but having difficulty determining totals of items in Table C from 2 tables A & B below based on the effective date.
Table A

WTH*	SG	EffectiveDate*
fr	3	01/01/2010
ox	1	01/01/2010
fr	5	05/01/2012
ox	2	05/01/2012

表B

Table B

EQ	WTH	FTR	EffectiveDate
A	fr	30	01/01/2010
A	ox	10	01/01/2010
B	fr	50	05/01/2010
B	ox	40	05/01/2010
A	fr	60	31/12/2011
A	ox	15	31/12/2011
B	fr	35	05/02/2013
B	ox	5	05/02/2013

表C

Table C

EQ	WTH	FTR	SG	TOTAL
A	fr	30	3	90
A	ox	10	1	10
B	fr	50	3	150
B	ox	40	1	40
A	fr	60	3	180
A	ox	15	1	15
B	fr	35	5	175
B	ox	5	2	10

WTH *和EffectiveDate *是主键



我尝试过：



目前，这是我的代码;

WTH* and EffectiveDate* are the primary keys

What I have tried:

Currently, this is my code;

Select Q2.EQ, Q1.WTH, Q2.FTR Q1.SG, [Q1.SG]*[Q2.FTR] AS Total
FROM Table A Q1 INNER JOIN Table B Q2
ON Q1.WTH = Q2.WTH AND Q1.EffectiveDate >= Q2.EffectiveDate

推荐答案

这是一个连接。你想做一个工会吗？工会将这两张桌子连在一起。联接将一个表中的数据过滤到与第二个表匹配的记录

This is a join. Do you want to do a union? A union would join the two tables together. A join filters down the data in one table to records that also match the second table

这篇关于如何根据生效日期计算2个表中的总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！