php - 子查询和主查询中的日期之前

Question

我有两张 table :客户 table 和事件 table 。我试图找出有多少客户有 2 个特定事件。例如，有多少客户被购买并到达分行。 (我在事件表中记录了客户所做的每一项行为。)这是查询:

SELECT COUNT(*) as total FROM activity
WHERE activity = 'arrived' AND
customerid IN
(SELECT DISTINCT(customerid) FROM activity
WHERE activity = 'bought')

但我需要知道他们到达后购买了多少商品，因为有些顾客是从网站上购买的，而不是在一年后才到达分行。所以我只想要到达并购买的顾客。我试过这个:

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

但它不起作用......有什么想法吗？

Answer 1

你的方向错了。您不应该使用子查询。相反，您应该尝试自加入。这是我的解决方案:

--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