自连接表视图 [英] Self joining table view

问题描述

我有一个包含非唯一潜在客户的表格，我需要对其进行分组以包含唯一的、最新的(列date)潜在客户.

I have a table containing non-unique leads, which I need to group to contain unique, most recent (column date) leads.

id      lead_id status  date
-----   ------  --      -------------------
26199   666842  Ok      2013-06-19 12:00:09

56199   376842  Ok      2013-06-19 12:00:09
58322   376842  Ok      2013-06-21 12:11:59
60357   376842  Ok      2013-06-24 12:22:00
61431   376842  Ok      2013-06-25 12:18:02
62365   376842  Ok      2013-06-26 12:16:04
63202   376842  Ok      2013-06-27 12:14:08
63983   376842  Er      2013-06-28 12:12:06

所以在上面的例子中，我应该有两条线索:id 26199 和 63983 因为它们都是 MAX(date) 而 GROUP BY Lead_id.

So in the example above I should have two leads as a result: id 26199 and 63983 as they both are the ones with MAX(date) while being GROUP BY lead_id.

我尝试了左连接、最大值和组聚合，不知道我做错了什么.

I tried with left joins, max and group aggregation, don't know what I'm doing wrong.

SELECT a.lead_id, MAX(a.created) AS created FROM RawLead a LEFT JOIN RawLead b ON b.created = a.created GROUP BY a.lead_id

不幸的是我不能使用子查询，因为我需要在视图中显示它们.

Unfortunatelly I cannot use subqueries, cause I need to present them in the view.

推荐答案

无子查询 :)

select
l1.*
from
lead l1
left join lead l2 on l1.date < l2.date and l1.lead_id = l2.lead_id
where l2.id is null 

在 sqlfiddle 中查看它的实时运行情况.

See it working live in an sqlfiddle.

LEFT JOIN 的工作原理是，当 l1.date 处于最大值时，没有 l2.date 具有更大的值，并且 l2 行的值将为 NULL.

The LEFT JOIN works on the basis that when l1.date is at its maximum value, there is no l2.date with a greater value and the l2 rows values will be NULL.

这篇关于自连接表视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！