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

本文介绍了SQL左联接:选择一对多关系中的最后一条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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