如何在SQL Server中只匹配一行？

产品ID
发票编号
金额

对于每个表中只有一行符合这些条件的条目，一切都正常。
但是有时候，在这些列中有几行具有相同的值（如上所述），但具有唯一的ID（这不是错误，而是应该的方式）。
它的问题是，当加入它时，会产生笛卡尔积。
要重新创建表，请在此处输入以下语句：

DROP TABLE IF EXISTS Revenues
GO

CREATE TABLE Revenues 
(
    RevenueID [nvarchar](10) NULL,  
    ProductID [nvarchar](10) NULL,  
    InvoiceNo [nvarchar](10) NULL,      
    Amount money NULL
)
GO

DROP TABLE IF EXISTS CashIns
GO

CREATE TABLE CashIns 
(
    CashinID [nvarchar](10) NULL,
    ProductID [nvarchar](10) NULL,  
    InvoiceNo [nvarchar](10) NULL,      
    Amount money NULL
)
GO

INSERT INTO [Revenues] VALUES ('123', '456', '987', 1000)
INSERT INTO [Revenues] VALUES ('234', '456', '987', 1000)

INSERT INTO [CashIns] VALUES ('ABC', '456', '987', 1000)
INSERT INTO [CashIns] VALUES ('BCD', '456', '987', 1000)

SELECT 
    R.RevenueID,
    R.ProductID,
    R.InvoiceNo,
    R.Amount,
    C.CashinID,
FROM 
    [Revenues] R
LEFT JOIN 
    [CashIns] C ON R.ProductID = C.ProductID
                AND R.InvoiceNo = C.InvoiceNo
                AND R.Amount = C.Amount

SELECT
    *
FROM 
    [Revenues] R
OUTER APPLY 
    (SELECT TOP(1) *
     FROM [CashIns] C) C

正如我在注解中所说的，您的数据关系存在一个根本问题。您需要在一个表中引用另一个表的唯一标识符。如果不这样做，则只能对两个表中的事务进行排序，并按行号联接它们。您是在使用希望和祈祷来联接数据，而不是使用不可靠的标识符。

--This example orders the transactions in each transaction table and uses
--the order number to join them.
WITH RevPrelim AS (
    SELECT *
      , ROW_NUMBER() OVER(PARTITION BY InvoiceNo, ProductID, Amount ORDER BY RevenueID) AS row_num
    FROM [Revenues] R
), CashinsPrelim AS (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY InvoiceNo, ProductID, Amount ORDER BY CashinID) AS row_num
    FROM [CashIns] AS C
)
SELECT *
FROM RevPrlim AS r
    LEFT OUTER JOIN CashinsPrelim AS c
        ON c.ProductID = r.ProductID
            AND c.InvoiceNo = r.InvoiceNo
            AND c.Amount = r.Amount
            AND c.row_num = r.row_num

如何在SQL Server中只匹配一行？

1条答案

相关问题

热门标签

最新问答