日期:2014-05-18 浏览次数:20589 次
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
------解决方案--------------------
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')