sql-server - 目前在 SQL Server 中使用多个左连接 - 有更简单的方法吗？

Question

我有一个生成正确结果的 SQL 语句，但是该语句很长而且可能格式不正确。有人对缩短声明有什么建议吗？

SELECT 
    COUNT(b.priceRRP) AS tomi, 
    COUNT(c.priceRRP) AS mac, 
    COUNT(d.priceRRP) AS well, 
    COUNT(bt.priceTrade) AS tomiTrade, 
    COUNT(ct.priceTrade) AS macTrade, 
    COUNT(dt.priceTrade) AS wellTrade
FROM 
    Items AS a
RIGHT JOIN 
    PriceT AS pt ON pt.internalid = a.internal_id
LEFT JOIN 
    Pricewell AS d ON d.idn = (SELECT TOP(1) idn FROM Pricewell 
                               WHERE pt.idn = item 
                                 AND CASE WHEN a.taxcode = 1 THEN a.retailPrice 
                                          ELSE a.retailPrice * 1.2 END > priceRRP 
                                 AND priceRRP <> 0 
                                 AND syncDate = '2015-10-01' 
                               ORDER BY syncDate DESC, syncTime DESC)
LEFT JOIN 
    Pricetomi AS b ON b.idn = (SELECT TOP(1) idn FROM Pricetomi 
                               WHERE pt.idn = item 
                                 AND CASE WHEN a.taxcode = 1 THEN a.retailPrice 
                                          ELSE a.retailPrice * 1.2 END > priceRRP 
                                 AND priceRRP <> 0 
                                 AND syncDate = '2015-10-01' 
                               ORDER BY syncDate DESC, syncTime DESC)
LEFT JOIN 
    Pricemac AS c ON c.idn = (SELECT TOP(1) idn FROM Pricemac 
                              WHERE pt.idn = item 
                                AND CASE WHEN a.taxcode = 1 THEN a.retailPrice 
                                         ELSE a.retailPrice * 1.2 END > priceRRP 
                                AND priceRRP <> 0 
                                AND syncDate = '2015-10-01' 
                              ORDER BY syncDate DESC, syncTime DESC)
LEFT JOIN 
    Pricewell AS dt ON dt.idn = (SELECT TOP(1) idn FROM Pricewell 
                                 WHERE pt.idn = item 
                                   AND CASE WHEN a.taxcode = 1 THEN a.tradePrice 
                                            ELSE a.tradePrice * 1.2 END > priceTrade 
                                   AND priceTrade <> 0 
                                   AND syncDate = '2015-10-01' 
                                 ORDER BY syncDate DESC, syncTime DESC)
LEFT JOIN 
    Pricetomi AS bt ON bt.idn = (SELECT TOP(1) idn FROM Pricetomi 
                                 WHERE pt.idn = item 
                                   AND CASE WHEN a.taxcode = 1 THEN a.tradePrice 
                                            ELSE a.tradePrice * 1.2 END > priceTrade 
                                   AND priceTrade <> 0 
                                   AND syncDate = '2015-10-01' 
                                 ORDER BY syncDate DESC, syncTime DESC)
LEFT JOIN 
    Pricemac AS ct ON ct.idn = (SELECT TOP(1) idn FROM Pricemac 
                                WHERE pt.idn = item 
                                  AND CASE WHEN a.taxcode = 1 THEN a.tradePrice 
                                           ELSE a.tradePrice * 1.2 END > priceTrade 
                                  AND priceTrade <> 0 
                                  AND syncDate = '2015-10-01' 
                                ORDER BY syncDate DESC, syncTime DESC)
WHERE 
    a.inactive = 0
    AND a.discontinued = 0

谢谢克里斯

Answer 1

我不认为你可以缩短这么多。请注意，您可以为此使用 outer apply:

SELECT ...
FROM PriceT AS pt
LEFT JOIN Items AS a ON pt.internalid = a.internal_id
OUTER APPLY (SELECT TOP(1) priceRRP FROM Pricewell ...) d
OUTER APPLY (SELECT TOP(1) priceRRP FROM Pricetomi ...) b
...
WHERE ...

还有很多人发现使用 left join 而不是 right join 更容易理解。