问题描述

我正在尝试编写 SQL 来生成购买了产品 B 但从未购买过产品 C 的唯一用户的计数.

I am trying to write the SQL to generate the count of unique users who have purchased product B but have never purchased product C.

我有一个解决方案，但它太丑了，我敢打赌有更好的方法来做到这一点.关于如何在最佳实践方面做得更好的任何想法?

I have a solution but its so ugly I bet there is a better way of doing it. Any thoughts on how to make this better in terms of best practices?

SELECT Count(*) AS bought_b_no_c
FROM   (SELECT user_id,
               Sum(bought_b_no_c) AS boolean_b_no_c
        FROM   (SELECT user_id,
                       product,
                       CASE
                         WHEN product = 'B' THEN 1
                         WHEN product = 'C' THEN -1
                         ELSE 0
                       END AS bought_b_no_c
                FROM   table_purchases) AS T
        GROUP  BY user_id) AS J
WHERE  boolean_b_no_c = 1

我正在编辑，因为似乎很少有答案具有诸如 SUM(product = 'C') 之类的操作，但这会返回错误，例如: Incorrect syntax near ')' 并突出显示 SUM 函数周围的结束括号在 C 旁边.

I am editing since there seem to be few answers that have operations such as SUM(product = 'C') but this returns an error such as: Incorrect syntax near ')' and it highlights the closing parathesis around the SUM function next to C.

推荐答案

你可以通过这个查询得到所有买了'B'但没有买'C'的用户:

You can get all the users who bought 'B' but not 'C' with this query:

SELECT user_id
FROM table_purchases
WHERE product IN ('B', 'C')
GROUP BY user_id
HAVING SUM(product = 'C') = 0;

那么你只需要统计它的结果:

Then you only need to count its results:

SELECT COUNT(*) AS bought_b_no_c
FROM (
  SELECT user_id
  FROM table_purchases
  WHERE product IN ('B', 'C')
  GROUP BY user_id
  HAVING SUM(product = 'C') = 0
) t;

这篇关于有没有更好的方法来代替这个任务的 2 个嵌套子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！