员工基本表--employee
workno employee indate(进厂日期) dimissiondate(离职日期) ...
员工在职时dimissiondate 字段为 NULL ,离职后加上离职日期表示此人已离职我现在要查询2008-7-1已前在职的员工,也就是说2008-7-1后离职的员工也要显示,因为7-1前这些员工还没离职。
workno employee indate(进厂日期) dimissiondate(离职日期) ...
员工在职时dimissiondate 字段为 NULL ,离职后加上离职日期表示此人已离职我现在要查询2008-7-1已前在职的员工,也就是说2008-7-1后离职的员工也要显示,因为7-1前这些员工还没离职。
select * from employee where dimissiondate>='2008/07/01' or (dimissiondate is null)
不提倡这样写
and isnull(dimissiondate,'')>'2008-7-1'
SQL codeSELECT * FROM TB WHERE indate < '2007-01-01' AND dimissiondate is null
(
workno varchar(10),
indate varchar(20),
dimissiondate varchar(20) null
)
insert into employeeselect '7','2008-07-02',null
union
select '1','2000-07-01',null
union
select '2','1990-07-01','2008-08-01'
union
select '3','2001-07-01','2007-06-01'
union
select '4','1950-07-01','2000-01-01'
union
select '5','1966-07-01',null
union
select '6','1998-07-01','2008-07-01'select * from employee where indate<'2008-07-01' and (dimissiondate is null or dimissiondate>'2008-07-01')drop table employee