SQL 中连接表时出现问题

Question

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN   (
            SELECT MerchID, MAX(moddate) AS LastCloseDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   LCD ON LCD.MerchID = M.MerchID
JOIN        (
            SELECT merchid ,avg(Transactions) ,avg(Profit) 
            FROM mwMaster.dbo.ResidualSummary RS
            WHERE RS.Date_Processed < LCD.LastCloseDate
            GROUP BY Merchid    
            )   R ON R.MerchID = M.MerchID 

我在执行以下连接时遇到问题。我以前遇到过这个问题并使用了临时表，但想找出我做错了什么。基本上，不起作用的线路是倒数第三条线路。 “< LCD.LastClosDate”表示它无法绑定。是否可以使用我在上面的嵌套查询中创建的 LCD 值（在该查询中我以类似的方式使用了 M 表，但没有遇到任何问题）？我想因为 LCD 表是在这里动态创建的，所以它不能在嵌套查询中使用，但这只是我的猜测。

有什么想法吗？

顺便说一句，我还看到有人使用 CROSS 和 OVER。不熟悉它是如何工作的，但可能适用于这里？

Answer 1

我认为虽然还没有测试过，你可以在 SQL 2005+ 中将 JOIN 更改为 CROSS APPLY

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN   (
            SELECT MerchID, MAX(moddate) AS LastCloseDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   LCD ON LCD.MerchID = M.MerchID
CROSS APPLY(
        SELECT merchid ,avg(Transactions) ,avg(Profit) 
        FROM mwMaster.dbo.ResidualSummary RS
        WHERE RS.Date_Processed < LCD.LastCloseDate
        GROUP BY Merchid    
        )   R ON R.MerchID = M.MerchID 

但使用 CTE 可能更容易

 WITH LCD AS (SELECT MerchID, MAX(moddate) AS LastCloseDate
        FROM mwMaster.dbo.MerchantStatusHistory
        GROUP BY MerchID),
  R AS (
              SELECT merchid ,avg(Transactions) ,avg(Profit) 
              FROM mwMaster.dbo.ResidualSummary RS
                   INNER JOIN LCD on 
                   LCD.MERCHID = RS.MERCHID
              WHERE RS.Date_Processed < LCD.LastCloseDate
              GROUP BY Merchid    
            )

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN LCD ON LCD.MerchID = M.MerchID
LEFT JOIN R ON R.MerchID = M.MerchID 

Answer 2

如果没有您的数据，我无法真正测试这一点，但这是您可以做到的一种方法：

SELECT MID,
       MIN(moddate) OVER (PARTITION BY MerchID) as FirstOpenedDate,
       MAX(moddate) OVER (PARTITION BY MerchID) as LastCloseDate
FROM mwMaster.dbo.Merchant
HAVING DateProcessed < LastCloseDate