在子查询和主查询之前的日期
问题描述：
I'm have 2 tables: table for customers and table for activity. Im trying to find how many customers have 2 activity specific. For example, how many customers was bought and arrived to the branch. (i have line in the activity table for every act that the customer did.) this is the query:
SELECT COUNT(*) as total FROM activity
WHERE activity = 'arrived' AND
customerid IN
(SELECT DISTINCT(customerid) FROM activity
WHERE activity = 'bought')
but i need to know how much bought AFTER they arrvied, because there are customers that bought from the website, than arrived to the branch after 1 year for example. so i want only the customer that arrived and than bought. i tried this:
SELECT COUNT(*) as total, daten as odaten FROM activity
WHERE activity = 'arrived' AND
customerid IN
(SELECT DISTINCT(customerid), daten as tdaten FROM activity
WHERE activity = 'bought') HAVING odaten < tdaten
but its not working.. any idea?
答
You are in a wrong direction. You should not use sub query. Instead, you should try self join. Here is my solution:
--activity customerid daten
select count(customerid) as finalcount from(
select distinct t1.customerid
from tablename t1
inner join tablename t2 on t1.customerid=t2.customerid
where t1.activity='arrivived' and t2.activity='bought' and t1.daten<t2.daten
)t
答
You could try something like this
SELECT COUNT(a.*)
FROM activity a
INNER JOIN
(SELECT customerid, MAX(daten) as BoughtDate
FROM activity
WHERE activity = 'bought'
GROUP BY customerid) t
ON a.customerid = t.customerid
WHERE a.activity = 'arrived'
AND a.daten < t.BoughtDate