获取包括空在内的最新记录

Question

我在MS SQL服务器中有两个表

一个包含有关用户的信息

TBL_USER

UserID| Name         | Mail      |...many columns...|
------+--------------+---------- +------------------+
  292 | John Smith   | @@@@@@@@  |  ...             |
  293 | Anna Bennet  | @@@@@@@@  |  ...             |
  294 | Mark Johnson | @@@@@@@@  |  ...             |

其他表是每个用户付款的寄存器，UserID作为我的TBL_USER表的外键

TBL_PAYMENT

UserID| PaymentID | Amount | PaymentDate |
------+-----------+----------------------+
  292 |    782    |   378  | 02-17-2016  |
  293 |    783    |   172  | 03-22-2016  |
  292 |    784    |   395  | 03-28-2016  |
  292 |    785    |   358  | 04-01-2016  |
  293 |    786    |   826  | 05-07-2016  |
  293 |    787    |   835  | 05-23-2016  |

我想做的是一个查询语句或存储过程，当该用户没有像这样进行任何付款时，将每个用户的最新付款表和在同一结果表中引入 null 或任何其他静态值

UserID| Name         | PaymentID | Amount | PaymentDate |
------+--------------+---------- +--------+-------------+
  292 | John Smith   |    785    |  358   | 04-01-2016  |
  293 | Anna Bennet  |    786    |  786   | 05-07-2016  |
  294 | Mark Johnson |    NULL   |  NULL  |   NULL      |

现在我有以下声明，但这只有在每个用户完成一次付款时才能给我最后一次付款。我希望在同一张表中有一个失踪的报告。

SELECT
    usr.UserID,
    usr.UserName,
    pay.PaymentID,
    pay.Amount,
    pay.PaymentDate
FROM TBL_USER usr
    LEFT OUTER JOIN TBL_PAYMENT pay ON usr.UserID = pay.UserID
    INNER JOIN (
        SELECT UserID,MAX(PaymentDate) AS MAXDATE
        FROM TBL_PAYMENT Group by UserID
    ) latest ON pay.UserID = latest.UserID AND pay.PaymentDate = latest.MAXDATE

谁能帮我弄清楚这一点？

Answer 1

使用窗口函数：

SELECT usr.UserID, usr.UserName,
       pay.PaymentID, pay.Amount, pay.PaymentDate
FROM TBL_USER usr LEFT OUTER JOIN
     (SELECT pay.*,
             ROW_NUMBER() OVER (PARTITION BY userId Order by PaymentDate DESC) as seqnum
      FROM TBL_PAYMENT pay
     ) pay
      ON usr.UserID = pay.UserID AND seqnum = 1;

Answer 2

使用 WITH 子句更具可读性 - 并包含完整的测试数据：

WITH
-- input, don't use in real query
tbl_user(UserID,Name,Mail) AS (
          SELECT 292,'John Smith','@@@@@@@@'
UNION ALL SELECT 293,'Anna Bennet','@@@@@@@@'
UNION ALL SELECT 294,'Mark Johnson','@@@@@@@@'
)
,
tbl_payment(UserID,PaymentID,Amount,PaymentDate) AS (
          SELECT 292,782,378,DATE '2016-02-17'
UNION ALL SELECT 293,783,172,DATE '2016-03-22'
UNION ALL SELECT 292,784,395,DATE '2016-03-28'
UNION ALL SELECT 292,785,358,DATE '2016-04-01'
UNION ALL SELECT 293,786,826,DATE '2016-05-07'
UNION ALL SELECT 293,787,835,DATE '2016-05-23'
)
-- end of input - start real query with WITH,
-- not comma, here
, 
interesting_payments AS (
SELECT
  u.userid
, MAX(paymentdate) AS paymentdate
FROM tbl_user u
LEFT JOIN tbl_payment USING(userid)
GROUP BY u.userid
)
SELECT
  u.userid
, u.name
, p.paymentid
, p.amount
, p.paymentdate
FROM interesting_payments i
JOIN tbl_user u USING(userid)
LEFT JOIN tbl_payment p USING(userid,paymentdate)
;

userid|name        |paymentid|amount|paymentdate
   292|John Smith  |      785|   358|2016-04-01
   293|Anna Bennet |      787|   835|2016-05-23
   294|Mark Johnson|NULL     |NULL  |NULL

Answer 3

如果每个用户的付款日期不重复，这应该可以正常工作

    SELECT 
TBL_USER.UserId, TBL_USER.Name, pm.PaymentId, pm.Amount, pm.PaymentDate
FROM TBL_USER 
LEFT JOIN (
    SELECT
    U.USERID,.MAX(P.PaymentDate) AS MAXDATE
    FROM TBL_USER U
    INNER JOIN TBL_PAYMENT P on U.UserID = P.UserID
    group by U.USERID
) J
on TBL_USER.UserID = J.UserID
LEFT JOIN TBL_PAYMENT Pm on Pm.PaymentDate = J.MAXDATE