选择最早和最晚日期

本文介绍了选择最早和最晚日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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)