新手纠结了两天无结果的一个SQL查询
我用的sql 2008,存在这样一个表 name，数据如下：
分店 部门 工号状态 发生日期
522727100 112休假2007-04-10 00:00:00.000
522727100 112出差2004-03-02 00:00:00.000
522727100 112辞工2010-07-24 00:00:00.000
522727100 213辞工2006-05-01 00:00:00.000
522727100 214 正常 2002-01-15 00:00:00.000
522727100 214离职2005-01-15 00:00:00.000
522522111 712出差2005-05-01 00:00:00.000
522522111 712辞工2011-08-18 00:00:00.000
522522111 712出差2009-08-15 00:00:00.000
522522111 712休假2006-02-24 00:00:00.000
需要求得这样的结果：
如果某人具有[状态]为‘辞工’，而且按[发生日期]排序，‘辞工’之前最近一次状态为‘出差’，则列出他的所有的记录。例如以上绿色部分需要列出。 而红色部分不要列出（因为按[发生日期]排序，‘辞工’之前最近一次记录不是‘出差’，而是‘休假’;并且虽然红、绿两部分工号相同，但因[分点]、[部门]的不同应区别为不同的人）
刚接触这个啊，纠结了两晚没头绪。
帮我指点迷津，谢谢。
[解决办法]
create table t1( fendian int, bumen int, gonghao int, zhuangtai varchar(10), fashengriqi datetime)insert into t1select 522727100, 1, 12, '休假', '2007-04-10 00:00:00.000' union allselect 522727100, 1, 12, '出差', '2004-03-02 00:00:00.000' union allselect 522727100, 1, 12, '辞工', '2010-07-24 00:00:00.000' union allselect 522727100, 2, 13, '辞工', '2006-05-01 00:00:00.000' union allselect 522727100, 2, 14, '正常', '2002-01-15 00:00:00.000' union allselect 522727100, 2, 14, '离职', '2005-01-15 00:00:00.000' union allselect 522522111, 7, 12, '出差', '2005-05-01 00:00:00.000' union allselect 522522111, 7, 12, '辞工', '2011-08-18 00:00:00.000' union allselect 522522111, 7, 12, '出差', '2009-08-15 00:00:00.000' union allselect 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-1and 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 gonghao522522111 7 12 出差 2005-05-01 00:00:00.000 522522111 7 12522522111 7 12 辞工 2011-08-18 00:00:00.000 522522111 7 12522522111 7 12 出差 2009-08-15 00:00:00.000 522522111 7 12522522111 7 12 休假 2006-02-24 00:00:00.000 522522111 7 12
[解决办法]
--调整下;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-1and 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 fashengriqi522522111 7 12 出差 2005-05-01 00:00:00.000522522111 7 12 辞工 2011-08-18 00:00:00.000522522111 7 12 出差 2009-08-15 00:00:00.000522522111 7 12 休假 2006-02-24 00:00:00.000
[解决办法]
--sql 2000用子查询完成.
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 ,'辞工', '2006-05-01 00:00:00.000')insert into tb values('522727100', 2 ,14 ,'正常', '2002-01-15 00:00:00.000')insert into tb values('522727100', 2 ,14 ,'离职', '2005-01-15 00:00:00.000')insert into tb values('522522111', 7 ,12 ,'出差', '2005-05-01 00:00:00.000')insert into tb values('522522111', 7 ,12 ,'辞工', '2011-08-18 00:00:00.000')insert into tb values('522522111', 7 ,12 ,'出差', '2009-08-15 00:00:00.000')insert into tb values('522522111', 7 ,12 ,'休假', '2006-02-24 00:00:00.000')goselect t1.* from tb t1,(select m.* from( select t.*,px=(select count(1) from tb where 分店 = t.分店 and 部门 = t.部门 and 工号 = t.工号 and 发生日期 < t.发生日期 ) + 1 From tb t) m,( select t.*,px=(select count(1) from tb where 分店 = t.分店 and 部门 = t.部门 and 工号 = t.工号 and 发生日期 < t.发生日期 ) + 1 From tb t) nwhere m.分店 = n.分店 and m.部门 = n.部门 and m.工号 = n.工号and m.状态 = '辞工' and n.状态 = '出差' and m.px = n.px + 1) t2where t1.分店 = t2.分店 and t1.部门 = t2.部门 and t1.工号 = t2.工号order by t1.分店 ,t1.部门 ,t1.工号,t1.发生日期drop table tb/*分店 部门 工号 状态 发生日期 -------------------- ----------- ----------- ---------- ------------------------------------------------------ 522522111 7 12 出差 2005-05-01 00:00:00.000522522111 7 12 休假 2006-02-24 00:00:00.000522522111 7 12 出差 2009-08-15 00:00:00.000522522111 7 12 辞工 2011-08-18 00:00:00.000（所影响的行数为 4 行）*/
[解决办法]
--sql 2005用row_number()实现.
create table tb(分店 varchar(20), 部门 int,工号 int,状态 nvarchar(10),发生日期 datetime)insert into tb values('522727100', 1 ,12 ,N'休假', '2007-04-10 00:00:00.000')insert into tb values('522727100', 1 ,12 ,N'出差', '2004-03-02 00:00:00.000')insert into tb values('522727100', 1 ,12 ,N'辞工', '2010-07-24 00:00:00.000')insert into tb values('522727100', 2 ,13 ,N'辞工', '2006-05-01 00:00:00.000')insert into tb values('522727100', 2 ,14 ,N'正常', '2002-01-15 00:00:00.000')insert into tb values('522727100', 2 ,14 ,N'离职', '2005-01-15 00:00:00.000')insert into tb values('522522111', 7 ,12 ,N'出差', '2005-05-01 00:00:00.000')insert into tb values('522522111', 7 ,12 ,N'辞工', '2011-08-18 00:00:00.000')insert into tb values('522522111', 7 ,12 ,N'出差', '2009-08-15 00:00:00.000')insert into tb values('522522111', 7 ,12 ,N'休假', '2006-02-24 00:00:00.000')goselect t1.* from tb t1,(select m.* from( select t.*,px=row_number() over(partition by 分店 ,部门 ,工号 order by 发生日期) + 1 From tb t) m,( select t.*,px=row_number() over(partition by 分店 ,部门 ,工号 order by 发生日期) + 1 From tb t) nwhere m.分店 = n.分店 and m.部门 = n.部门 and m.工号 = n.工号and m.状态 = N'辞工' and n.状态 = N'出差' and m.px = n.px + 1) t2where t1.分店 = t2.分店 and t1.部门 = t2.部门 and t1.工号 = t2.工号order by t1.分店 ,t1.部门 ,t1.工号,t1.发生日期drop table tb/*分店 部门 工号 状态 发生日期-------------------- ----------- ----------- ---------- -----------------------522522111 7 12 出差 2005-05-01 00:00:00.000522522111 7 12 休假 2006-02-24 00:00:00.000522522111 7 12 出差 2009-08-15 00:00:00.000522522111 7 12 辞工 2011-08-18 00:00:00.000(4 行受影响)*/