试试:SELECT *
FROM crm_opportunity o
LEFT JOIN ( SELECT opp_id ,
id ,
MAX(create_date) create_date
FROM crm_customer_trace
WHERE create_date > '2013-01-01'
GROUP BY opp_id ,
id
) c ON o.opp_id = c.opp_id
WHERE o.opp_name LIKE '%beijing%'
FROM crm_opportunity o
LEFT JOIN ( SELECT opp_id ,
id ,
MAX(create_date) create_date
FROM crm_customer_trace
WHERE create_date > '2013-01-01'
GROUP BY opp_id ,
id
) c ON o.opp_id = c.opp_id
WHERE o.opp_name LIKE '%beijing%'
FROM crm_opportunity o
LEFT JOIN ( SELECT opp_id ,
MAX(create_date) create_date
FROM crm_customer_trace
WHERE create_date > '2013-01-01'
GROUP BY opp_id
) c ON o.opp_id = c.opp_id
WHERE o.opp_name LIKE '%beijing%'
可以顺利摔选出来数据,但是字表create_date > '2013-01-01和为空的数据也一起出来了,如何光显示create_date > '2013-01-01'不包含为空的数据
FROM crm_opportunity o
LEFT JOIN ( SELECT opp_id ,
MAX(create_date) create_date
FROM crm_customer_trace
WHERE create_date > '2013-01-01'
GROUP BY opp_id
) c ON o.opp_id = c.opp_id
WHERE o.opp_name LIKE '%beijing%' and c.create_date > '2013-01-01'
from crm_opportunity a
inner join
(select * from crm_customer_track c
where c.create_date>'2013-1-1' and
not exists(select 1 from crm_customer_track d
where d.create_date>'2013-1-1'
and d.opp_id=c.opp_id
and d.create_date>c.create_date)) b on a.opp_id=b.opp_id
where a.opp_name like '%beijing%'