使用max（列）和另一列> 0连接表(Join tables using max(column) with a with another column >0)

 我有两张桌子，我正在尝试为我们的筹款部门建立一个查询，但我正在努力。 使用SQL Server 2008。 
 
 上诉表中包含有关我们将向哪些成员发送捐款申诉的数据。 
 
 Yearly_Gift表包含会员每年捐赠多少钱的数据。 有时，此表可以为特定年份提供零美元金额。 
 
 我正在尝试做的是使用这两个表来提出有人捐赠的最后一年以及他们捐赠了多少，只要它大于零，我们发出的呼吁。 
 
 以下是表格和一些数据的简化版本。 我也包括我想要的输出。 谁能帮我这个？ 
 
--Build Tables

CREATE TABLE [dbo].[Appeals](
    [Appeal_ID] [int] NOT NULL,
    [Member_ID] [int] NOT NULL,
 CONSTRAINT [PK_Appeals] PRIMARY KEY CLUSTERED 
(
    [Appeal_ID] ASC,
    [Member_ID] ASC
)WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY     =     OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Yearly_Gift](
    [Member_ID] [int] NOT NULL,
    [FiscalYear] [char](4) NOT NULL,
    [Amount] [money] NULL,
 CONSTRAINT [PK_Yearly_Gift] PRIMARY KEY CLUSTERED 
(
    [Member_ID] ASC,
    [FiscalYear] ASC
)WITH (PAD_INDEX  = OFF, 
 STATISTICS_NORECOMPUTE  = OFF, 
 IGNORE_DUP_KEY =     OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


--Fill tables

INSERT INTO Appeals VALUES (1,101)
INSERT INTO Appeals VALUES (1,102)
INSERT INTO Appeals VALUES (2,101)
INSERT INTO Appeals VALUES (2,102)
INSERT INTO Appeals VALUES (2,103)
INSERT INTO Appeals VALUES (2,104)
INSERT INTO Appeals VALUES (2,105)

INSERT INTO Yearly_Gift VALUES(101,'2015',100)
INSERT INTO Yearly_Gift VALUES(102,'2014',0)
INSERT INTO Yearly_Gift VALUES(102,'2012',150)
INSERT INTO Yearly_Gift VALUES(102,'2011',200)
INSERT INTO Yearly_Gift VALUES(103,'2013',500)
INSERT INTO Yearly_Gift VALUES(103,'2014',500)
INSERT INTO Yearly_Gift VALUES(104,'2012',200)
INSERT INTO Yearly_Gift VALUES(104,'2015',100)
 
 期望的输出 
 
Appeal_ID  Member_ID  FiscalYear  Amount
2          101         2015       100
2          102         2012       150
2          103         2014       500   
2          104         2015       100 
2          105         NULL       NULL  
 
 感谢您提供的任何帮助。 

I have two tables that I’m trying to build a query with for our fundraising department but that I’m struggling with. Using SQL Server 2008.
 
The Appeals table holds data about which members we are going to send an appeal for a donation to.
 
The Yearly_Gift table holds data about how much money a member donated on a yearly basis. Sometimes this table can have a zero dollar amount for a specific year.
 
What I’m trying to do is use these two tables to come up with the last year that someone donated and how much they donated, provided that it was greater than zero, for the appeal we are sending out.
 
Here are simplified versions of the tables and some data. I’m also including my desired output. Can anyone help me with this?
 
--Build Tables

CREATE TABLE [dbo].[Appeals](
    [Appeal_ID] [int] NOT NULL,
    [Member_ID] [int] NOT NULL,
 CONSTRAINT [PK_Appeals] PRIMARY KEY CLUSTERED 
(
    [Appeal_ID] ASC,
    [Member_ID] ASC
)WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY     =     OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Yearly_Gift](
    [Member_ID] [int] NOT NULL,
    [FiscalYear] [char](4) NOT NULL,
    [Amount] [money] NULL,
 CONSTRAINT [PK_Yearly_Gift] PRIMARY KEY CLUSTERED 
(
    [Member_ID] ASC,
    [FiscalYear] ASC
)WITH (PAD_INDEX  = OFF, 
 STATISTICS_NORECOMPUTE  = OFF, 
 IGNORE_DUP_KEY =     OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


--Fill tables

INSERT INTO Appeals VALUES (1,101)
INSERT INTO Appeals VALUES (1,102)
INSERT INTO Appeals VALUES (2,101)
INSERT INTO Appeals VALUES (2,102)
INSERT INTO Appeals VALUES (2,103)
INSERT INTO Appeals VALUES (2,104)
INSERT INTO Appeals VALUES (2,105)

INSERT INTO Yearly_Gift VALUES(101,'2015',100)
INSERT INTO Yearly_Gift VALUES(102,'2014',0)
INSERT INTO Yearly_Gift VALUES(102,'2012',150)
INSERT INTO Yearly_Gift VALUES(102,'2011',200)
INSERT INTO Yearly_Gift VALUES(103,'2013',500)
INSERT INTO Yearly_Gift VALUES(103,'2014',500)
INSERT INTO Yearly_Gift VALUES(104,'2012',200)
INSERT INTO Yearly_Gift VALUES(104,'2015',100)
 
Desired Output
 
Appeal_ID  Member_ID  FiscalYear  Amount
2          101         2015       100
2          102         2012       150
2          103         2014       500   
2          104         2015       100 
2          105         NULL       NULL  
 
Thank you for any help that you can provide.

原文：https://stackoverflow.com/questions/33830470