从第二个表中只加入一行，如果不存在行返回null

Question

在此查询中，我需要显示左表中的所有记录，并且只显示右表中所有结果为最高日期的记录。

目前查询：

SELECT a.*, c.* 
FROM users a 
INNER JOIN payments c 
    ON a.id = c.user_ID 
INNER JOIN 
(
    SELECT user_ID, MAX(date) maxDate 
    FROM payments 
    GROUP BY user_ID 
) b ON c.user_ID = b.user_ID AND 
     c.date = b.maxDate 
WHERE a.package = 1 

这将返回所有记录中，其中加入是有效的，但我要显示所有用户，如果他们没有付款尚未从支付表中的字段为空。

我可以用一个工会来显示其他行：

SELECT a.*, c.* 
FROM users a 
INNER JOIN payments c 
    ON a.id = c.user_ID 
INNER JOIN 
(
    SELECT user_ID, MAX(date) maxDate 
    FROM payments 
    GROUP BY user_ID 
) b ON c.user_ID = b.user_ID AND 
     c.date = b.maxDate 
WHERE a.package = 1 
union 
SELECT a.*, c.* 
FROM users a 
--here I would need to join with payments table to get the columns from the payments table, 
but where the user doesn't have a payment yet 
WHERE a.package = 1 

选择使用工会似乎并不像一个很好的解决方案，但是这是我的尝试。

Answer 1

所以，换句话说，您需要一个用户列表和最后一笔付款。

您可以使用OUTER APPLY而不是INNER JOIN来获取每个用户的最后付款。性能可能会更好，它将按照您希望的方式针对没有付款的用户开展工作。

SELECT a.*, b.* 
FROM users a 
OUTER APPLY (SELECT * FROM payments c 
       WHERE c.user_id = a.user_id 
       ORDER BY c.date DESC 
       FETCH FIRST ROW ONLY) b 
WHERE a.package = 1; 

这是一个通用版本的相同的概念，不需要你的表（对于其他读者）。它给出了每个用户的数据库用户列表和最近修改的对象。您可以正确地看到包含没有对象的用户。

SELECT a.*, b.* 
FROM all_users a 
OUTER APPLY (SELECT * FROM all_objects b 
       WHERE b.owner = a.username 
       ORDER BY b.last_ddl_time desc 
       FETCH FIRST ROW ONLY) b 

Answer 2

我喜欢@Matthew麦克皮克答案，但OUTER APPLY是12C或更高，是不是很地道的甲骨文，历史反正。这里有一个直LEFT OUTER JOIN版本：

SELECT * 
FROM users a 
LEFT OUTER JOIN 
(
    -- retrieve the list of payments for just those payments that are the maxdate per user 
    SELECT payments.* 
    FROM payments 
    JOIN (SELECT user_id, MAX(date) maxdate 
      FROM payments 
      GROUP BY user_id 
     ) maxpayment_byuser 
     ON maxpayment_byuser.maxdate = payments.date 
      AND maxpayment_byuser.user_id = payments.user_id 
) b ON a.ID = b.user_ID 

如果性能是一个问题，你会发现下面的更高性能，但为了简单起见，你会用一个额外的“的maxDate”最终列。

SELECT * 
FROM users a 
LEFT OUTER JOIN 
(
    -- retrieve the list of payments for just those payments that are the maxdate per user 
    SELECT * 
    FROM (
      SELECT payments.*, 
        MAX(date) OVER (PARTITION BY user_id) maxdate 
      FROM payments 
     ) max_payments 
    WHERE date = maxdate 
) b ON a.ID = b.user_ID 

Answer 3

使用row_number()一个通用的方法是“最高日”或“最近”或类似条件下非常有用：

SELECT 
     * 
FROM users a 
LEFT OUTER JOIN (
     -- determine the row corresponding to "most recent" 
     SELECT 
      payments.* 
      , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) is_recent 
     FROM payments 
) b ON a.ID = b.user_ID 
     AND b.is_recent = 1 

（扭转了子句中的ORDER BY也使“最老”）