我正在嘗試撰寫 SQL 來生成購買了產品 B 但從未購買過產品 C 的唯一用戶數。
| 用戶身份 | 產品 | 購買日期 |
|---|---|---|
| 1 | 一個 | 2015-01-10 00:00:00.000 |
| 1 | 乙 | 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 | 乙 | 2015-02-15 00:00:00.000 |
| 3 | D | 2014-09-23 00:00:00.000 |
| 3 | 乙 | 2014-06-01 00:00:00.000 |
| 4 | 乙 | 2014-12-14 00:00:00.000 |
| 4 | F | 2015-03-03 00:00:00.000 |
有人建議嘗試以下方法
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;
但是，SQL Server 說我有如下錯誤：
訊息 102，級別 15，狀態 1，第 5 行 ')' 附近的語法不正確。
任何人都知道為什么會發生這種情況以及如何準確了解它是如何作業的？
PS 這是我的原始代碼
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
uj5u.com熱心網友回復：
你可以用這個：
SELECT COUNT(b.*) AS bought_b_no_c
FROM table_purchases b
WHERE b.product = 'B'
AND b.user_id NOT IN (
SELECT c.user_id
FROM table_purchases c
WHERE c.product = 'C'
)
這個“讀”對我來說更自然。
有人建議的另一個解決方案可以與 Dale K 的修復一起使用：
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(case when product = 'C' then 1 else 0 end) = 0
) t;
我不確定哪一個具有更好的性能，但兩者都應該可以解決問題。
uj5u.com熱心網友回復：
這是一種使用 NOT EXISTS 的方法。我翻譯了“獲得使用產品 B 且未購買產品 C 的唯一用戶”的問題
select count(distinct x.user) /*gets unique users*/
from table x
where x.product='B' /*who purchased product B*/
and not exists(select null
from table y
where y.user=x.user
and x.product = 'C' /*and the user has not purchased C */)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/395680.html
標籤：sql sql-server 查询语句 划分
上一篇：SQLServer日期列不一致