SQL左联接:选择一对多关系中的最后一条记录 [英] SQL Left join: selecting the last records in a one-to-many relationship

问题描述

我有一个客户表和一个明细表.

I have a customer table, and a detail table.

我想为表中的每个客户提取一条记录，并在适用的情况下显示该客户的最新详细数据.

I want to want to pull a record for every customer in the table and show the latest detail data on that customer where applicable.

目前，我的where子句正在过滤掉客户.

Currently my where clause is filtering out customers.

我曾尝试将where子句移到左侧外部联接中，但无法获得所需的结果.

I have tried moving the where clause into the left outer join, but I have not been able to get the desired result.

当我运行查询时，它似乎根本没有过滤产品.

It does not seem to filter the product at all when I run the query.

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
  cust.customer = inv.customer
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.customer < inv2.customer
      )
WHERE 
      (
          inv.Product = 'CC' 
       OR inv.Product = 'CG' 
       OR inv.Product = 'CH'
      ) 
  AND inv2.customer IS NULL

我的问题类似于

SQL连接:选择一对多关系中的最后记录

我正在尝试同一件事，只是希望包括每个客户并按产品进行过滤.

I'm trying for the same thing just want to include every customer and filter by product.

更新

样本数据

这是我的原始查询，这很不错，除了我想念客户

Here is my Original Query, which is great except for I am missing customers

如果我删除where子句并按如下所示将其插入到左连接中

If I remove the where clause and insert it into the left join as follows

LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
           inv.date < inv2.date 
       OR  inv.date = inv2.date AND inv.customer < inv2.customer
      ) 
  AND (    
           inv.Product = 'CC' 
       OR  inv.Product = 'CHECK' 
       OR  inv.Product = 'ACH'
      )

这是结果 有显示的产品列不是"CC"等.并且客户是重复的.

Here is the result There are product columns showing up that are not 'CC' etc.. And the customers are duplicated.

推荐答案

您几乎完全正确.

您的第一个查询将删除所有没有指定产品详细信息的客户，因为您没有在第一个OUTER JOIN的ON条件下指定产品过滤器.

Your first query removes all customers that don't have details with the specified product, because you didn't specifiy the product filter in the ON condition of the first OUTER JOIN.

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.customer < inv2.customer
      )
WHERE 
  inv2.customer IS NULL

应该这样做.

还有另一件事我认为不太正确. AND inv.customer < inv2.customer部分可能应该是AND inv.id < inv2.id(如果detail表中有一个id字段).

There is one other thing I think is not quite correct. The AND inv.customer < inv2.customer part should probably be AND inv.id < inv2.id (if there is an id field in the detail table).

这是因为OR条件正在通过主键过滤具有相同日期的detail记录.

That's because the OR condition is filtering the detail records that have the same date by their primary key.

更新

由于该表没有主键字段，因此可以使用ROWID ADS功能来解决此问题:

Since the table in question has no primary key field you can use the ROWID ADS feature to solve that:

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.ROWID < inv2.ROWID
      )
WHERE 
  inv2.customer IS NULL

这篇关于SQL左联接:选择一对多关系中的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！