我正在嘗試撰寫 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,
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') 之類的操作，但這會回傳一個錯誤，例如：')' 附近的語法不正確，并且它突出顯示了 C 旁邊 SUM 函式周圍的閉包.
uj5u.com熱心網友回復：
您可以通過以下查詢獲取所有購買了“B”但未購買“C”的用戶：
SELECT user_id
FROM table_purchases
WHERE product IN ('B', 'C')
GROUP BY user_id
HAVING SUM(product = 'C') = 0;
那么你只需要統計它的結果：
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;
uj5u.com熱心網友回復：
一個簡單的聚合將在這里完成：
SELECT user_id
FROM table_purchases
GROUP BY user_id
HAVING SUM(product = 'B') > 0 AND SUM(product = 'C') = 0;
我們也可以使用存在邏輯來表達這一點：
SELECT DISTINCT user_id
FROM table_purchases t1
WHERE product = 'B' AND
NOT EXISTS (SELECT 1 FROM table_purchases t2
WHERE t2.user_id = t1.user_id AND
t2.product = 'C');
uj5u.com熱心網友回復：
對不起，我是在手機上寫的，所以沒有詳細解釋。
SELECT COUNT(DISTINCT t1.user_id) AS bought_b_no_c
FROM table_purchases t1
LEFT JOIN table_purchases t2 ON t1.user_id = t2.user_id AND t2.product = 'C'
WHERE t2.user_id IS NULL AND t1.product = 'B';
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/393639.html
上一篇：我不知道如何通過鏈接此資料庫中的資料來正確獲取Laravel中的資料
下一篇：將迭代列添加到結果集