有没有更好的方法来代替这个任务的 2 个嵌套子查询?

2022-01-23 00:00:00 sql database subquery mysql

我正在尝试编写 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.

<头>
user_id产品购买日期
1一个2015-01-10 00:00:00.000
1B2014-11-23 00:00:00.000
1C2015-05-01 00:00:00.000
2一个2014-10-01 00:00:00.000
2C2014-12-23 00:00:00.000
3B2015-02-15 00:00:00.000
3D2014-09-23 00:00:00.000
3E2014-06-01 00:00:00.000
4E2014-12-14 00:00:00.000
4F2015-03-03 00:00:00.000

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

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;

相关文章