表1结构如下：
run_id prcs_id use_id diliver_time flow_id end_time
231 1 45467 2011-09-14 15:47:00 1 null
231 2 67676 2011-09-14 16:35:12 1 null
231 3 67676 null 1 null
235 1 45467 null 3 null
235 2 67376 2011-11-14 16:35:12 3 null
235 3 45554 2011-11-07 11:06:57 3 null
235 4 45454 null 3 null
238 8 45454 2011-11-07 11:06:27 6 null
239 3 46554 2011-11-07 11:06:57 6 2011-11-07 13:06:57
239 4 46454 2011-11-07 12:06:57 6 2011-11-07 13:06:57
239 5 25454 2011-11-07 13:06:57 6 2011-11-07 13:06:57
236 1 35467 2011-11-17 15:47:00 7 2011-11-22 13:06:57
236 2 67376 2011-11-19 16:35:12 7 2011-11-22 13:06:57
236 3 46d54 2011-11-20 11:06:57 7 2011-11-22 13:06:57
236 4 46c54 2011-11-21 12:06:57 7 2011-11-22 13:06:57
236 5 25454 2011-11-22 13:06:57 7 2011-11-22 13:06:57
266 1 25654 2011-11-22 16:06:57 8 null
996 1 35467 2011-01-17 15:47:00 3 null
996 2 67376 2011-11-19 16:35:12 3 null
1.我想查找出diliver_time字段为1那行数据，结果如下：
结果1：
run_id prcs_id use_id diliver_time flow_id end_time
266 1 25654 2011-11-22 16:06:57 8 null
2、我想把prcs_id列单独为1出现的数据全部过滤掉
结果2：
run_id prcs_id use_id diliver_time flow_id end_time
231 1 45467 2011-09-14 15:47:00 1 null
231 2 67676 2011-09-14 16:35:12 1 null
231 3 67676 null 1 null
235 1 45467 null 3 null
235 2 67376 2011-11-14 16:35:12 3 null
235 3 45554 2011-11-07 11:06:57 3 null
235 4 45454 null 3 null
238 8 45454 2011-11-07 11:06:27 6 null
239 3 46554 2011-11-07 11:06:57 6 2011-11-07 13:06:57
239 4 46454 2011-11-07 12:06:57 6 2011-11-07 13:06:57
239 5 25454 2011-11-07 13:06:57 6 2011-11-07 13:06:57
236 1 35467 2011-11-17 15:47:00 7 2011-11-22 13:06:57
236 2 67376 2011-11-19 16:35:12 7 2011-11-22 13:06:57
236 3 46d54 2011-11-20 11:06:57 7 2011-11-22 13:06:57
236 4 46c54 2011-11-21 12:06:57 7 2011-11-22 13:06:57
236 5 25454 2011-11-22 13:06:57 7 2011-11-22 13:06:57
996 1 35467 2011-01-17 15:47:00 3 null
996 2 67376 2011-11-19 16:35:12 3 null
3、我想把prcs_id列单独为不连续出现(比如1,2,3……)的那组（相同run_id）数据全部过滤掉
结果3：
run_id prcs_id use_id diliver_time flow_id end_time
231 1 45467 2011-09-14 15:47:00 1 null
231 2 67676 2011-09-14 16:35:12 1 null
231 3 67676 null 1 null
235 1 45467 null 3 null
235 2 67376 2011-11-14 16:35:12 3 null
235 3 45554 2011-11-07 11:06:57 3 null
235 4 45454 null 3 null
236 1 35467 2011-11-17 15:47:00 7 2011-11-22 13:06:57
236 2 67376 2011-11-19 16:35:12 7 2011-11-22 13:06:57
236 3 46d54 2011-11-20 11:06:57 7 2011-11-22 13:06:57
236 4 46c54 2011-11-21 12:06:57 7 2011-11-22 13:06:57
236 5 25454 2011-11-22 13:06:57 7 2011-11-22 13:06:57
996 1 35467 2011-01-17 15:47:00 3 null
996 2 67376 2011-11-19 16:35:12 3 null
3、我想把prcs_id列单独为一行那组（相同run_id）数据全部过滤掉
结果4：
run_id prcs_id use_id diliver_time flow_id end_time
231 1 45467 2011-09-14 15:47:00 1 null
231 2 67676 2011-09-14 16:35:12 1 null
231 3 67676 null 1 null
235 1 45467 null 3 null
235 2 67376 2011-11-14 16:35:12 3 null
235 3 45554 2011-11-07 11:06:57 3 null
235 4 45454 null 3 null
239 3 46554 2011-11-07 11:06:57 6 2011-11-07 13:06:57
239 4 46454 2011-11-07 12:06:57 6 2011-11-07 13:06:57
239 5 25454 2011-11-07 13:06:57 6 2011-11-07 13:06:57
236 1 35467 2011-11-17 15:47:00 7 2011-11-22 13:06:57
236 2 67376 2011-11-19 16:35:12 7 2011-11-22 13:06:57
236 3 46d54 2011-11-20 11:06:57 7 2011-11-22 13:06:57
236 4 46c54 2011-11-21 12:06:57 7 2011-11-22 13:06:57
236 5 25454 2011-11-22 13:06:57 7 2011-11-22 13:06:57
996 1 35467 2011-01-17 15:47:00 3 null
996 2 67376 2011-11-19 16:35:12 3 null
------解决方案--------------------
再回复一下：
- SQL code
declare @表1 table (run_id int,prcs_id int,use_id varchar(5),diliver_time datetime,flow_id int,end_time datetime)insert into @表1select 231,1,'45467','2011-09-14 15:47:00',1,null union allselect 231,2,'67676','2011-09-14 16:35:12',1,null union allselect 231,3,'67676',null,null,null union allselect 235,1,'45467',null,null,null union allselect 235,2,'67376','2011-11-14 16:35:12',3,null union allselect 235,3,'45554','2011-11-07 11:06:57',3,null union allselect 235,4,'45454',null,null,null union allselect 238,8,'45454','2011-11-07 11:06:27',6,null union allselect 239,3,'46554','2011-11-07 11:06:57',6,'2011-11-07 13:06:57' union allselect 239,4,'46454','2011-11-07 12:06:57',6,'2011-11-07 13:06:57' union allselect 239,5,'25454','2011-11-07 13:06:57',6,'2011-11-07 13:06:57' union allselect 236,1,'35467','2011-11-17 15:47:00',7,'2011-11-22 13:06:57' union allselect 236,2,'67376','2011-11-19 16:35:12',7,'2011-11-22 13:06:57' union allselect 236,3,'46d54','2011-11-20 11:06:57',7,'2011-11-22 13:06:57' union allselect 236,4,'46c54','2011-11-21 12:06:57',7,'2011-11-22 13:06:57' union allselect 236,5,'25454','2011-11-22 13:06:57',7,'2011-11-22 13:06:57' union allselect 266,1,'25654','2011-11-22 16:06:57',8,null union allselect 996,1,'35467','2011-01-17 15:47:00',3,null union allselect 996,2,'67376','2011-11-19 16:35:12',3,null union allselect 999,3,'67376','2011-11-19 16:35:12',3,null union all --这两条数据是我添加上的，测试用的select 999,5,'67376','2011-11-19 16:35:12',3,null --3、我想把prcs_id列单独为不连续出现(比如1,2,3……)的那组（相同run_id）数据全部过滤掉;with maco as(select row_number() over (partition by run_id order by prcs_id) as rid, * from @表1)select run_id ,prcs_id ,use_id ,diliver_time,flow_id ,end_time from maco where run_id not in (select distinct run_id from maco group by prcs_id-rid,run_id having(count(1)=1))/*run_id prcs_id use_id diliver_time flow_id end_time----------- ----------- ------ ----------------------- ----------- -----------------------231 1 45467 2011-09-14 15:47:00.000 1 NULL231 2 67676 2011-09-14 16:35:12.000 1 NULL231 3 67676 NULL NULL NULL235 1 45467 NULL NULL NULL235 2 67376 2011-11-14 16:35:12.000 3 NULL235 3 45554 2011-11-07 11:06:57.000 3 NULL235 4 45454 NULL NULL NULL239 3 46554 2011-11-07 11:06:57.000 6 2011-11-07 13:06:57.000239 4 46454 2011-11-07 12:06:57.000 6 2011-11-07 13:06:57.000239 5 25454 2011-11-07 13:06:57.000 6 2011-11-07 13:06:57.000236 1 35467 2011-11-17 15:47:00.000 7 2011-11-22 13:06:57.000236 2 67376 2011-11-19 16:35:12.000 7 2011-11-22 13:06:57.000236 3 46d54 2011-11-20 11:06:57.000 7 2011-11-22 13:06:57.000236 4 46c54 2011-11-21 12:06:57.000 7 2011-11-22 13:06:57.000236 5 25454 2011-11-22 13:06:57.000 7 2011-11-22 13:06:57.000996 1 35467 2011-01-17 15:47:00.000 3 NULL996 2 67376 2011-11-19 16:35:12.000 3 NULL*/