选择最早和最晚日期 [英] Select the earliest and latest dates

问题描述

所以我有一个表，其结构如下:

So I have a table which is structured in the following format:

in_click  first_name      create_date 
100       joe             2011-10-01 10:01
100       joe             2011-10-01 10:05
100       joe             2011-10-01 10:07
100       joe             2011-10-01 10:08
100       joe             2011-10-01 10:10
101       sara            2011-10-01 10:15
101       sara            2011-10-01 10:17
101       sara            2011-10-01 10:20
101       sara            2011-10-01 10:22

对于每个名字，我想选择第一行和最后一行. 因此它应该是名字为joe的第一个日期和最后一个日期 其中的名字叫乔.有点像if-else语句，但是 我只是不确定如何在MySQL中获取该信息.

For each first name, I want to select the first and last rows. So it should be the first date with the name joe and the last date where the first name is joe. Kinda like an if-else statement, but I'm just not sure how to get that information in MySQL.

有帮助吗?！

我正在运行以下查询以获取所有信息:

I'm running the following query to just get everything:

SELECT t.in_click_id, t.keyword, c.lead_id, c.first_name, c.last_name, 
ls.create_date, ls.discriminator, l.affiliate_id 
FROM lead_status AS ls
INNER JOIN leads AS l ON l.id = ls.lead_id 
INNER JOIN tracker AS t ON l.in_click_id = t.in_click_id
INNER JOIN contacts AS c ON ls.lead_id = c.lead_id 
WHERE l.affiliate_id NOT IN('1002','1003')
AND ls.create_date BETWEEN '2011-11-09' AND '2011-11-10';

这就是我想要得到的:

in_click  first_name      create_date 
100       joe             2011-10-01 10:01
100       joe             2011-10-01 10:10
101       sara            2011-10-01 10:15
101       sara            2011-10-01 10:22

推荐答案

此方法适用于您提供的数据，不能说它可以在MySQL中使用，但可以在SQL Server中使用.

This works with the data you provided, can't say it would work in MySQL, but it works in SQL Server.

select t.in_click, 
    t.first_name,
    t.create_date
from tracker t
where 
    t.create_date = (select min(create_date) from tracker where in_click = t.in_click)
    or t.create_date = (select max(create_date) from tracker where in_click = t.in_click)

这篇关于选择最早和最晚日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！