有没有更好的方法来代替这个任务的 2 个嵌套子查询?-数据库问题

Is there a better method instead of 2 nested subqueries for this task?(有没有更好的方法来代替这个任务的 2 个嵌套子查询?)

本文介绍了有没有更好的方法来代替这个任务的 2 个嵌套子查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试编写 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
1	B	2014-11-23 00:00:00.000
1	C	2015-05-01 00:00:00.000
2	一个	2014-10-01 00:00:00.000
2	C	2014-12-23 00:00:00.000
3	B	2015-02-15 00:00:00.000
3	D	2014-09-23 00:00:00.000
3	E	2014-06-01 00:00:00.000
4	E	2014-12-14 00:00:00.000
4	F	2015-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;

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