在mysql嵌套select语句上卡住

Question

我在select语句中连接了三个表：

表CRM_CONTACTS结构：

CONTACTS_ID, CONTACTS_EMAIL
1            email@email.com
2            email2@email.com

表CRM_PRODUCTS结构：

PRODUCTS_ID, PRODUCTS_NAME, PRODUCTS_TYPE
204          Sample         free_sample
205          beginners_1    monthly_subscription
206          beginners_2    monthly_subscription  

表CRM_PRODUCTS_PURCHASE：

ID, CONTACTS_ID, PRODUCTS_ID
3   1            204
4   1            205
5   2            204

因此，客户当然可以购买多个产品。

我想做一个select语句，选择所有购买了PRODUCT_ID 204 (免费样本)的客户，但如果他们购买了PRODUCT_TYPE=monthly_subscription的产品，我不希望他们出现在我的结果中

所以我想要的预期输出是与CONTACTS_ID 2的联系。

我是sql语句的新手。这就是我到目前为止得到的：

    SELECT CRM_PRODUCTS_PURCHASE.CONTACTS_ID,CRM_PRODUCTS_PURCHASE.PRODUCTS_ID, CRM_CONTACTS.CONTACTS_EMAIL, CRM_CONTACTS.CONTACTS_LANGUAGE
FROM CRM_PRODUCTS_PURCHASE
LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID
LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID
WHERE CRM_PRODUCTS_PURCHASE.CONTACTS_ID IN 
(SELECT CRM_CONTACTS.ID
    FROM CRM_PRODUCTS_PURCHASE
        LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID
        LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID
    WHERE CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = 204 AND
        CRM_CONTACTS.CONTACTS_EMAIL!='' AND
        NOT coalesce(CRM_CONTACTS.CONTACTS_DEACTIVATED,0)
        GROUP BY CRM_CONTACTS.CONTACTS_EMAIL
        ORDER BY CRM_CONTACTS.CONTACTS_LANGUAGE)

Answer 1

首先，您提到了ORDER BY CRM_CONTACTS.CONTACTS_LANGUAGE，它不在SELECT语句中。如何在返回单列的子查询中使用GROUP BY。

(SELECT CRM_CONTACTS.ID
    FROM CRM_PRODUCTS_PURCHASE
        LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID
        LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID
    WHERE CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = 204 AND
        CRM_CONTACTS.CONTACTS_EMAIL!='' AND
        NOT coalesce(CRM_CONTACTS.CONTACTS_DEACTIVATED,0)
        GROUP BY CRM_CONTACTS.CONTACTS_EMAIL
        ORDER BY CRM_CONTACTS.CONTACTS_LANGUAGE)

Answer 2

您应该尽可能避免嵌套子选择，因为它们对于MySQL内部优化器来说很难使用，因此往往会生成非常慢的查询。在许多情况下，创建一个包含子select数据的临时表并连接它通常比使用子select更好，但这是根据情况而定的。

但是，有时您需要这样做，在这种情况下，请尽量保持子选择的简单。

SELECT *
FROM CRM_PRODUCTS_PURCHASE
   # Do other joins 
WHERE
    CRM_PRODUCTS_PURCHASE.CONTACTS_ID NOT IN (
        SELECT CONTACTS_ID
        FROM CRM_PRODUCTS_PURCHASE
        WHERE
            CRM_PRODUCTS_PURCHASE.PRODUCTS_ID IN (205, 206))
    AND CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = 204
;

以上是未经测试的

注意:子选择中的ORDER BY通常不起作用。将订单保留到最后，即mysql - order by inside subquery

在我们的实际案例中，我们经常发现将上面的查询作为2个查询来运行要好几个数量级。例如，生成一个CONTACTS_ID列表并将该值存储在一个PHP变量中，然后进行第二个查询，该查询在NOT in子句中使用这些值。