if object_id('student') is not null drop table student
create table student 
(S_id int identity(1,1),S_Name varchar(4),S_Date datetime,S_Flag int)
insert into student
select '张三','2012-01-01',1 union all
select '李四','2012-01-01',1 union all
select '王五','2012-01-01',1 union all
select '赵六','2012-01-01',1 union all
select '张三','2012-02-01',1 union all
select '李四','2012-02-01',1 union all
select '王五','2012-02-01',0 union all
select '赵六','2012-02-01',0 


--Student表的所有数据

S_Id    S_Name    S_Date        S_Flag

1    张三    2012-01-01    1
2    李四    2012-01-01    1
3    王五    2012-01-01    1
4    赵六    2012-01-01    1    
5    张三    2012-02-01    1
6    李四    2012-02-01    1
7    王五    2012-02-01    0
8    赵六    2012-02-01    0



问题：SQL语句如何查询出下列结果？

满足条件 在 S_Date = '2012-02-01'期间  S_Flag=0  同时在 S_Date='2012-01-01' 也存在数据的同学

查询结果
S_Id    S_Name    S_Date        S_Flag

3    王五    2012-01-01    1
4    赵六    2012-01-01    1
7    王五    2012-02-01    0
8    赵六    2012-02-01    0

select * from student where s_name in (
select s_name from student where (s_date='2012-02-01' and s_flag=0)) and s_date='2012-01-01'
union 
select *  from student where s_date='2012-02-01' and s_flag=0

------解决方案--------------------
SQL code
select
 * 
from
 student t 
where
 exists(select 1 from student where S_Name=t.S_Name 
and
 exists (select 1 from student where s_name=t.s_name and S_Flag=0  and S_Date = '2012-02-01') 
and
 S_Date = '2012-01-01')