时间戳

首页 > 解决方案 > SQL 是 LEFT JOIN 和 WHERE 语句的最佳替代方案？

sql-server - SQL 是 LEFT JOIN 和 WHERE 语句的最佳替代方案？

问题描述

基本上我有两张桌子。客户和采购表。我的问题是购买表非常大并导致性能问题，我试图将我的代码组织到相关的 CTE 中。

我正在尝试为那些购买了 A 型吉他或没有购买的人提取所有购买记录。

我想过滤掉任何没有购买 GuitarType A但仍保留没有购买任何东西的客户。

这是我的代码：

WITH Purchases AS 
(
    SELECT
        , CustID
        , GuitarType
    FROM
        Purchase
    WHERE
        GuitarType = 'A'
)
,
RelevantCustomers AS
(
    SELECT
        P.Custid
        , P.PurchaseDate
        , C.CustType
    FROM
        Customer

)
SELECT
    Custid
    , GuitarType
    , PurchaseDate
FROM
    Purchases AS p
INNER JOIN
    RelevantCustomers AS rc ON p.CustId= rc.CustId

顾客：

  +--------+-------------+----------+
    | CustId | CreatedDate | CustType |
    +--------+-------------+----------+
    | 1      | 01/01/2017  | A        |
    +--------+-------------+----------+
    | 2      | 01/01/2018  | B        |
    +--------+-------------+----------+
    | 4      | 01/01/2018  | C        |
    +--------+-------------+----------+

购买

+----------+--------------+------------+
| GuitarId | PurchaseDate | GuitarType |
+----------+--------------+------------+
| 1        | 04/01/2018   | A          |
+----------+--------------+------------+
| 1        | 05/01/2018   | A          |
+----------+--------------+------------+
| 1        | 06/01/2018   | C          |
+----------+--------------+------------+
| 2        | 06/01/2018   | A          |
+----------+--------------+------------+
| 2        | 06/01/2018   | B          |
+----------+--------------+------------+
| 2        | 06/01/2018   | A          |
+----------+--------------+------------+

如果我使用 INNER JOIN 那么它只会返回那些购买 Guitar Type A 的人。如果我使用 LEFT 那么它将包括所有客户。

One alternative is to move the "Where GuitarType = 'A' down to the where clause and do a LEFT JOIN but this will cause my code to be unorganized and potentially some performance issues.

标签： sql-servertsql

解决方案

This might do it

SELECT rc.Custid, p.GuitarType, p.PurchaseDate
  FROM RelevantCustomers rc 
  LEFT JOIN Purchases p 
    ON p.CustId = rc.CustId
  LEFT JOIN Purchases pn 
    ON pn.CustId = rc.CustId 
   AND p.GuitarType != 'A'
 WHERE (p.GuitarType = 'A' OR p.CustID IS NULL)  
   and  pn.CustID is null

推荐阅读