我正在尝试编写 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.
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 个嵌套子查询?
发布评论