create table t1
(
    fendian int,
    bumen int,
    gonghao int,
    zhuangtai varchar(10),
    fashengriqi datetime
)
insert into t1
select 522727100, 1, 12, '休假', '2007-04-10 00:00:00.000' union all
select 522727100, 1, 12, '出差', '2004-03-02 00:00:00.000' union all
select 522727100, 1, 12, '辞工', '2010-07-24 00:00:00.000' union all
select 522727100, 2, 13, '辞工', '2006-05-01 00:00:00.000' union all
select 522727100, 2, 14, '正常', '2002-01-15 00:00:00.000' union all
select 522727100, 2, 14, '离职', '2005-01-15 00:00:00.000' union all
select 522522111, 7, 12, '出差', '2005-05-01 00:00:00.000' union all
select 522522111, 7, 12, '辞工', '2011-08-18 00:00:00.000' union all
select 522522111, 7, 12, '出差', '2009-08-15 00:00:00.000' union all
select 522522111, 7, 12, '休假', '2006-02-24 00:00:00.000'
select * from t1

;with aaa as
(select ROW_NUMBER() over(partition by fendian,bumen,gonghao order by fashengriqi) as rowindex,* from t1)
,bbb as
(select a.fendian,a.bumen,a.gonghao from aaa as a inner join aaa as b on a.rowindex=b.rowindex-1
and a.fendian=b.fendian and a.bumen=b.bumen and a.gonghao=b.gonghao and b.zhuangtai='辞工' and a.zhuangtai='出差')
select * from t1 as a1 inner join bbb as b1 on a1.fendian=b1.fendian and a1.bumen=b1.bumen and a1.gonghao=b1.gonghao

--------------------------
fendian    bumen    gonghao    zhuangtai    fashengriqi    fendian    bumen    gonghao
522522111    7    12    出差    2005-05-01 00:00:00.000    522522111    7    12
522522111    7    12    辞工    2011-08-18 00:00:00.000    522522111    7    12
522522111    7    12    出差    2009-08-15 00:00:00.000    522522111    7    12
522522111    7    12    休假    2006-02-24 00:00:00.000    522522111    7    12

------解决方案--------------------
SQL code

--调整下
;with aaa as
(select ROW_NUMBER() over(partition by fendian,bumen,gonghao order by fashengriqi) as rowindex,* from t1)
,bbb as
(select a.fendian,a.bumen,a.gonghao from aaa as a inner join aaa as b on a.rowindex=b.rowindex-1
and a.fendian=b.fendian and a.bumen=b.bumen and a.gonghao=b.gonghao and b.zhuangtai='辞工' and a.zhuangtai='出差')
select a1.* from t1 as a1 inner join bbb as b1 on a1.fendian=b1.fendian and a1.bumen=b1.bumen and a1.gonghao=b1.gonghao

------------------------------
fendian    bumen    gonghao    zhuangtai    fashengriqi
522522111    7    12    出差    2005-05-01 00:00:00.000
522522111    7    12    辞工    2011-08-18 00:00:00.000
522522111    7    12    出差    2009-08-15 00:00:00.000
522522111    7    12    休假    2006-02-24 00:00:00.000

------解决方案--------------------
--sql 2000用子查询完成.
SQL code
create table tb(分店 varchar(20), 部门 int,工号 int,状态 varchar(10),发生日期 datetime)
insert into tb values('522727100', 1 ,12 ,'休假', '2007-04-10 00:00:00.000')
insert into tb values('522727100', 1 ,12 ,'出差', '2004-03-02 00:00:00.000')
insert into tb values('522727100', 1 ,12 ,'辞工', '2010-07-24 00:00:00.000')
insert into tb values('522727100', 2 ,13 ,