9/5 每日一題(MYSQL 尋找沒有任何交易的訪問用戶,並計算他們訪問次數)

 

# Write your MySQL query statement below

SELECT V.customer_id , COUNT(V.customer_id) AS count_no_trans

FROM Visits AS V

LEFT JOIN Transactions AS T ON V.visit_id  = T.visit_id

WHERE T.visit_id IS NULL

# 子查詢篩選條件: 沒有交易紀錄的

GROUP BY V.customer_id

-------------------------

select customer_id,count(visit_id) as count_no_trans
#這邊選擇兩列, 一個訪問用戶的customer_id,另一個是對應用戶visit_id的訪問次數

from Visits
where visit_id not in (select visit_id from Transactions)

#這是查尋的核心部分,子查詢中從 Transactions 表中所出所有visit_id
#然後檢查Visits 表中的visit_id是否不再這個子查詢結果中, 
#也就是這個條件過濾了Visits表中沒有對應交易的訪問紀錄 

group by customer_id;

#按照customer_id 進行分組, 這樣才可以得到每個用戶的未進行交易的訪問次數