SQL：在两个日期之间获取唯一数据

Hi I am using a mysql table "student" which has 4 columns:

嗨,我使用的是一个有4列的mysql表“student”:

Id Name   Date          StudentId
1  John   2010-01-15      3
2  Matt   2010-01-10      5
3  Jane   2010-02-10      8
4  John   2010-02-11      3
5  Matt   2010-02-11      5
6  Jane   2010-02-11      8

I want to fetch only new entries in the table between 2010-02-10 and 2010-02-12. If a student had a previous entry in the table then the query should not return that value. So in the above case the query should only return both entries of Jane since John and Matt had an entry each previous to the date specified.

我想在2010-02-10和2010-02-12之间仅获取表中的新条目。如果学生在表中有先前的条目,则查询不应返回该值。因此,在上面的情况下,查询应该只返回Jane的两个条目,因为John和Matt在指定的日期之前都有一个条目。

This is what I have but it is not working:

这就是我所拥有的,但它不起作用:

SELECT *  FROM student 
WHERE date(Date) 
between '2010-02-10' and '2010-02-12' 
and date(Date) 
not between '0000-00-00' and '2015-02-09';

1 个解决方案

#1

GROUP BY and HAVING is what you are looking for if you want single record per student:

如果您想要每个学生单次记录,GROUP BY和HAVING就是您要找的:

SELECT * FROM student 
  GROUP BY Name 
  HAVING DATE(Date) BETWEEN '2010-02-10' AND '2010-02-12';

Or I would use subquery if you want all the records:

或者如果你想要所有记录我会使用子查询:

SELECT * FROM student 
  WHERE DATE(Date) BETWEEN '2010-02-10' AND '2010-02-12'
  AND Name NOT IN 
     (SELECT DISTINCT Name FROM student WHERE DATE(Date) < '2010-02-10');

How it works: the subquery selects all the names that have records prior to the date range, i.e. the ones you don't want in your result. It produces set like ('John', 'Matt'). The main query then selects all the records in the given date range where Name NOT IN ('John', 'Matt').

工作原理:子查询选择所有在日期范围之前有记录的名称,即结果中不需要的名称。它产生类似('John','Matt')的集合。然后,主查询选择给定日期范围内的所有记录,其中Name NOT IN('John','Matt')。

#1