在我的数据库中,我有两个表:
操作
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| lead_id | int(11) | YES | UNI | NULL | |
| type | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
铅
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| status | varchar(255) | YES | | NULL | |
| created | datetime | NO | | NULL | |
| lead_id | int(11) | NO | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
导联表有点不寻常,因为导联是非唯一的,由lead_id
标识,所以可能有几个导联具有相同的lead_id
。
我想做的是检索所有操作,然后将它们与各自的潜在客户联系起来,条件是它们是最近的(MAX(created)
?)。
这个答案部分解释了如何处理leads表,但是我找不到如何以正确的方式连接子查询。
有多种方法可以做到这一点。以下内容将条件放入on
子句中:
select a.*
from lead l join
action a
on l.lead_id = a.lead_id and
l.created = (select MAX(created) from leads l2 where l2.lead_id = l.lead_id)
您也可以使用显式聚合子查询来完成此操作:
select a.*
from lead l join
action a
on l.lead_id = a.lead_id join
(select lead_id, max(created) as maxcreated
from lead l
group by lead_id
) lmax
on lmax.lead_id = l.lead_id and lmax.maxcreated = l.created;
未验证,但我认为您想要以下内容:
select action.*, max_leads.* from action left join
(select lead_id, max(created) from lead group by lead_id) max_leads
on action.lead_id = max_leads.lead_id