SQL COUNT between dates in two different column

问题:

Let's say, we have this table:

STUDENT |  START  | END
1       |1998-1-1 |2001-1-1
2       |1999-1-1 |2001-1-1
3       |2000-1-1 |2004-1-1
4       |2000-1-1 | NULL

I'm trying to do is:

Count number of students between start and end dates!

回答1:

Looks like you need to use a basic COUNT aggregate:

SELECT COUNT(Student)
FROM YourTable
WHERE Start >= @Start 
    AND End <= @End

I've used >= and <= respectively around the start and end date fields. Feel free to change to > or < as needed. It was unclear from your question whether you wanted between a specific field or if you were checking for a range between those two fields.

回答2:

Use the between Operator and COUNT aggregate function

SELECT COUNT(student) column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Between can be used with text so insert the dates where the values are,

Read more here if you still don't understand

EDIT : That should work, sorry about the error

http://www.w3schools.com/sql/sql_between.asp