跳过子表中的记录 [英] Skipping records in subtable

问题描述

我正在尝试编写查询，即从 2 个表中选择一些内容.不幸的是，我遇到了以下问题:在表中

I'm trying to write query, that is selecting few things from 2 tables. Unfortunately I stuck on following problem: in table

Person.BusinessEntityAdress

您将密钥存储到另一个包含人员地址的表中，但有些记录有 2 个或更多密钥，我的观点是只选择这些密钥中的一个，然后跳过其余部分.我正在使用 AdventureWorks2012 数据库.这是一个链接，您可以在其中阅读有关此数据库的信息.到目前为止，这是我的查询:

You store key to another table with people addresses, but some records got 2 or more keys, and my point is to select just one of those keys, and skip rest. I'm using AdventureWorks2012 Database. Here's a link, where you can read about this database. So far, this is my query:

SELECT 
    pp.FirstName AS CUS_FirstName,
    pp.FirstName + pp.LastName AS CUS_Name,
    pp.LastName AS CUS_LastName, 
    pa.AddressLine1 AS CUS_AddressLine1, 
    pa.AddressLine2 AS CUS_AddressLine2,
    pp.BusinessEntityID AS CUS_CleanOrgID, --ID klienta
    CUS_SRCOrgID = '0' --ID źródła danych
FROM Person.Person pp
LEFT JOIN Person.BusinessEntityAddress pb
ON pp.BusinessEntityID = pb.BusinessEntityID
LEFT JOIN Person.Address pa
    ON pb.AddressID=pa.AddressID
ORDER BY pp.BusinessEntityID;

推荐答案

如果有多个BusinessEntityAddress，您可以使用子选择限制为最新的BusinessEntityAddress一个人注意我想 Id 是 BusinessEntityAddress

You can use a subselect to limit to new newest BusinessEntityAddress in case of more than one BusinessEntityAddress for one Person N.B. I imagine that Id is the PK on BusinessEntityAddress

SELECT 
  pp.FirstName AS CUS_FirstName,
  pp.FirstName + pp.LastName AS CUS_Name,
  pp.LastName AS CUS_LastName, 
  pa.AddressLine1 AS CUS_AddressLine1, 
  pa.AddressLine2 AS CUS_AddressLine2,
  pp.BusinessEntityID AS CUS_CleanOrgID, --ID klienta
  CUS_SRCOrgID = '0' --ID źródła danych
FROM Person.Person pp
 LEFT JOIN Person.BusinessEntityAddress pb
  ON pp.BusinessEntityID = pb.BusinessEntityID
    AND NOT EXISTS (SELECT 1 FROM Person.BusinessEntityAddress pb2
                    WHERE pp.BusinessEntityID = pb2.BusinessEntityID
                      AND pb2.Id > pb.Id)
 LEFT JOIN Person.Address pa
  ON pb.AddressID=pa.AddressID
ORDER BY pp.BusinessEntityID;

这篇关于跳过子表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！