一条非常具有挑战性的Sql,大侠们进来吧……

表1：Emp_Tracking
有几条数据：
AccountNum SubAccount AccountType LastDateWithoutAmount LastDateWithAmount LastPurchaseAmount
----------- ----------- ----------- ----------------------- ----------------------- ---------------------
100 0 M NULL 2011-12-01 00:00:00.000 NULL
100 1 M NULL 2011-12-05 00:00:00.000 NULL
100 2 M NULL 2011-10-09 00:00:00.000 NULL
100 3 M NULL NULL NULL
100 4 M NULL NULL NULL
100 5 M NULL NULL NULL
101 0 M 2011-12-15 00:00:00.000 NULL NULL
101 1 M 2011-12-10 00:00:00.000 2011-12-01 00:00:00.000 NULL
101 2 M 2011-12-10 00:00:00.000 NULL NULL
101 3 M NULL NULL NULL

表2：Billing_SalesInvoice_Master
有几条数据：
id AccountNum SubAccount AccountType PurchaseDate InvoiceAmount
----------- ----------- ----------- ----------- ----------------------- ---------------------------------------
1 100 0 M 2011-12-01 00:00:00.000 200.00
2 100 1 M 2011-12-01 00:00:00.000 400.00
3 100 1 M 2011-12-05 00:00:00.000 600.00
4 100 2 M 2011-10-07 00:00:00.000 100.00
5 101 0 M 2011-12-03 00:00:00.000 0.00
6 101 1 M 2011-12-10 00:00:00.000 0.00
7 101 1 M 2011-12-01 00:00:00.000 200.00
8 101 2 M 2011-12-03 00:00:00.000 0.00
9 101 2 M 2011-12-10 00:00:00.000 0.00
10 101 0 M 2011-12-15 00:00:00.000 0.00
11 100 2 M 2011-10-09 00:00:00.000 100.00

Id为主键，自增

我现在要把Emp_Tracking表与Billing_SalesInvoice_Master表中AccountNum,SubAccount, AccountType对应的数据更新LastPurchaseAmount的值，这个值要在Billing_SalesInvoice_Master相对于的最后一个值，如：
100 - 0 - M 这个账号的LastPurchaseAmount应该是200.00（拿对应id最大的）
100 - 1 - M 这个账号的LastPurchaseAmount应该是600.00（拿对应id最大的）
101 - 1 - M 这个账号的LastPurchaseAmount应该是200.00（拿对应id最大的）
100 - 2 - M 这个账号的LastPurchaseAmount应该是100.00（拿对应id最大的）

不知道你们是否明白了我的意思，急急急……

SQL code

UPDATE 
   A
SET
   LastPurchaseAmount=B.InvoiceAmount
FROM
   Emp_Tracking A,
   Billing_SalesInvoice_Master B
WHERE
   A.AccountNum=B.AccountNum AND A.SubAccount=B.SubAccount AND  A.AccountType=B.AccountType
AND
   B. PurchaseDate =(SELECT MAX( PurchaseDate ) FROM Billing_SalesInvoice_Master WHERE AccountNum=B.AccountNum AND AND SubAccount=B.SubAccount AND  AccountType=B.AccountType)

SQL code

UPDATE Emp_Trackin
SET LastPurchaseAmount=B.PurchaseDate
FROM Emp_Tracking A,
   (SELECT AccountNum,SubAccount,MAX(PurchaseDate) PurchaseDate FROM Billing_SalesInvoice_Master GROUP BY AccountNum,SubAccount) B
WHERE  A.AccountNum=B.AccountNum AND A.SubAccount=B.SubAccount 

是这样么:
SQL code

create table Emp_Tracking(AccountNum int,SubAccount int,AccountType char(1),LastDateWithoutAmount datetime,LastDateWithAmount datetime,LastPurchaseAmount decimal(10,2))
insert into  Emp_Tracking select 100,0,'M',NULL,'2011-12-01 00:00:00.000',NULL
insert into  Emp_Tracking select 100,1,'M',NULL,'2011-12-05 00:00:00.000',NULL
insert into  Emp_Tracking select 100,2,'M',NULL,'2011-10-09 00:00:00.000',NULL
insert into  Emp_Tracking select 100,3,'M',NULL,NULL,NULL
insert into  Emp_Tracking select 100,4,'M',NULL,NULL,NULL
insert into  Emp_Tracking select 100,5,'M',NULL,NULL,NULL
insert into  Emp_Tracking select 101,0,'M','2011-12-15 00:00:00.000',NULL,NULL
insert into  Emp_Tracking select 101,1,'M','2011-12-10 00:00:00.000','2011-12-01 00:00:00.000',NULL
insert into  Emp_Tracking select 101,2,'M','2011-12-10 00:00:00.000',NULL,NULL
insert into  Emp_Tracking select 101,3,'M',NULL,NULL,NULL
create table Billing_SalesInvoice_Master(id int,AccountNum int,SubAccount int,AccountType char(1),PurchaseDate datetime,InvoiceAmount decimal(10,2))
insert into  Billing_SalesInvoice_Master select 1,100,0,'M','2011-12-01 00:00:00.000',200.00
insert into  Billing_SalesInvoice_Master select 2,100,1,'M','2011-12-01 00:00:00.000',400.00
insert into  Billing_SalesInvoice_Master select 3,100,1,'M','2011-12-05 00:00:00.000',600.00
insert into  Billing_SalesInvoice_Master select 4,100,2,'M','2011-10-07 00:00:00.000',100.00
insert into  Billing_SalesInvoice_Master select 5,101,0,'M','2011-12-03 00:00:00.000',0.00
insert into  Billing_SalesInvoice_Master select 6,101,1,'M','2011-12-10 00:00:00.000',0.00
insert into  Billing_SalesInvoice_Master select 7,101,1,'M','2011-12-01 00:00:00.000',200.00
insert into  Billing_SalesInvoice_Master select 8,101,2,'M','2011-12-03 00:00:00.000',0.00
insert into  Billing_SalesInvoice_Master select 9,101,2,'M','2011-12-10 00:00:00.000',0.00
insert into  Billing_SalesInvoice_Master select 10,101,0,'M','2011-12-15 00:00:00.000',0.00
insert into  Billing_SalesInvoice_Master select 11,100,2,'M','2011-10-09 00:00:00.000',100.00
go
update a set LastPurchaseAmount=b.InvoiceAmount 
from Emp_Tracking a inner join Billing_SalesInvoice_Master b on a.AccountNum=b.AccountNum and a.SubAccount=b.SubAccount and a.AccountType=b.AccountType
where not exists(select 1 from Billing_SalesInvoice_Master where AccountNum=b.AccountNum and SubAccount=b.SubAccount and AccountType=b.AccountType
and InvoiceAmount>b.InvoiceAmount)
select * from Emp_Tracking
/*
AccountNum  SubAccount  AccountType LastDateWithoutAmount   LastDateWithAmount      LastPurchaseAmount
----------- ----------- ----------- ----------------------- ----------------------- ---------------------------------------
100         0           M           NULL                    2011-12-01 00:00:00.000 200.00
100         1           M           NULL                    2011-12-05 00:00:00.000 600.00
100         2           M           NULL                    2011-10-09 00:00:00.000 100.00
100         3           M           NULL                    NULL                    NULL
100         4           M           NULL                    NULL                    NULL
100         5           M           NULL                    NULL                    NULL
101         0           M           2011-12-15 00:00:00.000 NULL                    0.00
101         1           M           2011-12-10 00:00:00.000 2011-12-01 00:00:00.000 200.00
101         2           M           2011-12-10 00:00:00.000 NULL                    0.00
101         3           M           NULL                    NULL                    NULL

(10 行受影响)

*/
go
drop table Emp_Tracking,Billing_SalesInvoice_Master