我正在尝试使用 SQL Server 2012 连接两个日志表，以便在记录操作时添加每个帐户的状态。
第一个表是根据请求的请求日期(request_date(和接收者(receiver_account_id(从网站用户(到其他用户(的联系请求日志中创建的报告。如果联系人请求获得批准，则还会填充批准日期 (approval_date( 列。此表如下所示：
receiver_account_id sender_account_id request_date approval_date
13 19 2012-08-10 2012-09-01
13 21 2013-05-15 NULL
17 19 2011-09-11 NULL
25 44 2012-11-05 2012-11-07
第二个表基本上记录了帐户的状态更改：
date account_id status
2011-07-10 13 free
2012-08-15 13 premium
2010-12-10 17 free
2012-05-05 17 premium
2010-01-20 25 free
2011-04-10 25 premium
2012-11-06 25 free
我想加入这两个表，所以我可以得到这样的东西：
receiver_account_id sender_account_id request_date approval_date receiver_status_when_received receiver_status_when_approved
13 19 2012-08-10 2012-09-01 free premium
13 21 2013-05-15 NULL premium NULL
17 19 2011-09-11 NULL free NULL
25 44 2012-11-05 2012-11-07 premium free
发件人的状态根本不重要。
试试这个
;WITH StatusPeriods AS
(
SELECT account_id, [Status], [date] AS StatusStart,
ISNULL((SELECT MIN(Date) FROM status_log nxt WHERE nxt.date > sl.Date AND nxt.account_id = sl.account_id), '2100-1-1') AS StatusEnd
FROM status_log sl
)
SELECT request.*,
StatusWhenReceived.[Status] AS receiver_status_when_received, StatusWhenApproved.[Status] AS receiver_status_when_approved
FROM requests
INNER JOIN StatusPeriods StatusWhenReceived
ON StatusWhenReceived.receiver_account_id = requests.account_id AND requests.request_date between StatusWhenReceived.StatusStart AND StatusWhenReceived.StatusEnd
LEFT JOIN StatusPeriods StatusWhenApproved
ON StatusWhenApproved.receiver_account_id = requests.account_id AND requests.approval_date between StatusWhenApproved.StatusStart AND StatusWhenApproved.StatusEnd