求一条sql语句

Question

表1结构如下：
runid prcsid use_id prcs_time    flow_id
231 1 45467 2011-09-14 15:47:00   1
231 2 67676 2011-09-14 16:35:12   1
231 3 45454 2011-11-07 11:06:57   1
235 1 45467 2011-11-14 15:47:00   3
235 2 67376 2011-11-14 16:35:12   3
235 3 45554 2011-11-07 11:06:57   3
235 4 45454 2011-11-07 11:06:57   3
238 1 45454 2011-11-07 11:06:27   6
239 1 35467 2011-10-14 15:47:00   6
239 2 67376 2011-11-14 16:35:12   6
239 3 46554 2011-11-07 11:06:57   6
239 4 46454 2011-11-07 11:06:57   6
239 5 25454 2011-11-07 11:06:57   6
236 1 35467 2011-11-17 15:47:00   7
236 2 67376 2011-11-19 16:35:12   7
236 3 46d54 2011-11-20 11:06:57   7
236 4 46c54 2011-11-21 11:06:57   7
236 5 25454 2011-11-22 11:06:57   7
266 1 25654 2011-11-22 16:06:57   8我想查找出prcs_time在11月1日到11月30日之间的数据并且prcsid字段为1那行数据的prcs_time列也必须为11月份的那组数据，并且我还想把prcsid列单独为1出现的数据全部过滤掉，即过滤掉非重复runid列数据，并且我还想判断flow_id为：1，3，6判断为上海，flow_id为7，8判断为北京结果如下：runid prcsid use_id prcs_time      区域235 1 45467 2011-11-14 15:47:00    上海
235 2 67376 2011-11-14 16:35:12    上海
235 3 45554 2011-11-07 11:06:57    上海
235 4 45454 2011-11-07 11:06:57    上海
236 1 35467 2011-11-17 15:47:00    北京
236 2 67376 2011-11-19 16:35:12    北京
236 3 46d54 2011-11-20 11:06:57    北京
236 4 46c54 2011-11-21 11:06:57    北京
236 5 25454 2011-11-22 11:06:57    北京
请各位大虾们赐教

Answer 1

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([runid] int,[prcsid] int,[use_id] nvarchar(5),[prcs_time] Datetime)
Insert #T
select 231,1,N'45467','2011-09-14 15:47:00' union all
select 231,2,N'67676','2011-09-14 16:35:12' union all
select 231,3,N'45454','2011-11-07 11:06:57' union all
select 235,1,N'45467','2011-11-14 15:47:00' union all
select 235,2,N'67376','2011-11-14 16:35:12' union all
select 235,3,N'45554','2011-11-07 11:06:57' union all
select 235,4,N'45454','2011-11-07 11:06:57' union all
select 239,1,N'35467','2011-10-14 15:47:00' union all
select 239,2,N'67376','2011-11-14 16:35:12' union all
select 239,3,N'46554','2011-11-07 11:06:57' union all
select 239,4,N'46454','2011-11-07 11:06:57' union all
select 239,5,N'25454','2011-11-07 11:06:57' union all
select 236,1,N'35467','2011-11-17 15:47:00' union all
select 236,2,N'67376','2011-11-19 16:35:12' union all
select 236,3,N'46d54','2011-11-20 11:06:57' union all
select 236,4,N'46c54','2011-11-21 11:06:57' union all
select 236,5,N'25454','2011-11-22 11:06:57'
Go
select a.* 
from #T as a
inner join 
(Select [runid]
from #T AS a
WHERE a.[prcs_time] >= '2011-11-01' AND a.[prcs_time]<'2011-12-01'
and EXISTS(SELECT 1 FROM #T WHERE runid=a.runid having a.[prcsid]=1 or a.prcsid=max([prcsid]))
group by [runid]
having COUNT(distinct prcsid)=2
)as b on a.[runid]=b.[runid]
/*
runid prcsid use_id prcs_time
235 1 45467 2011-11-14 15:47:00.000
235 2 67376 2011-11-14 16:35:12.000
235 3 45554 2011-11-07 11:06:57.000
235 4 45454 2011-11-07 11:06:57.000
236 1 35467 2011-11-17 15:47:00.000
236 2 67376 2011-11-19 16:35:12.000
236 3 46d54 2011-11-20 11:06:57.000
236 4 46c54 2011-11-21 11:06:57.000
236 5 25454 2011-11-22 11:06:57.000
*/

Answer 2

select
 *, 区域=case when flow_id in (1,3,6) then '上海' when flow_id in (7,8) then '北京' end
from
 tb a
where
 convert(varchar(7),prcs_time,120)='2011-11'
and
 not exists(select 1 from tb where runid=a.runid and prcsid=1 and convert(varchar(7),prcs_time,120)<>'2011-11')
and
 exists(select 1 from tb where runid=a.runid and prcsid>1)

Answer 3

以runid分组，所有记录都必髯在11月时用以上方法

Answer 4

create table tb(runid int,prcsid int,use_id varchar(10),prcs_time datetime,flow_id int)
insert into tb select 231,1,'45467','2011-09-14 15:47:00',1
insert into tb select 231,2,'67676','2011-09-14 16:35:12',1
insert into tb select 231,3,'45454','2011-11-07 11:06:57',1
insert into tb select 235,1,'45467','2011-11-14 15:47:00',3
insert into tb select 235,2,'67376','2011-11-14 16:35:12',3
insert into tb select 235,3,'45554','2011-11-07 11:06:57',3
insert into tb select 235,4,'45454','2011-11-07 11:06:57',3
insert into tb select 238,1,'45454','2011-11-07 11:06:27',6
insert into tb select 239,1,'35467','2011-10-14 15:47:00',6
insert into tb select 239,2,'67376','2011-11-14 16:35:12',6
insert into tb select 239,3,'46554','2011-11-07 11:06:57',6
insert into tb select 239,4,'46454','2011-11-07 11:06:57',6
insert into tb select 239,5,'25454','2011-11-07 11:06:57',6
insert into tb select 236,1,'35467','2011-11-17 15:47:00',7
insert into tb select 236,2,'67376','2011-11-19 16:35:12',7
insert into tb select 236,3,'46d54','2011-11-20 11:06:57',7
insert into tb select 236,4,'46c54','2011-11-21 11:06:57',7
insert into tb select 236,5,'25454','2011-11-22 11:06:57',7
insert into tb select 266,1,'25654','2011-11-22 16:06:57',8
go
select runid,prcsid,use_id,prcs_time,
(case when flow_id in(1,3,6) then '上海'
when flow_id in(7,8) then '北京'
end)as 区域
from tb a
where convert(varchar(7),prcs_time,120)='2011-11'
and not exists(select 1 from tb where runid=a.runid and prcsid=1 and convert(varchar(7),prcs_time,120)<>'2011-11')
and exists(select 1 from tb where runid=a.runid and prcsid>1)
/*
runid       prcsid      use_id     prcs_time               区域
----------- ----------- ---------- ----------------------- ----
235         1           45467      2011-11-14 15:47:00.000 上海
235         2           67376      2011-11-14 16:35:12.000 上海
235         3           45554      2011-11-07 11:06:57.000 上海
235         4           45454      2011-11-07 11:06:57.000 上海
236         1           35467      2011-11-17 15:47:00.000 北京
236         2           67376      2011-11-19 16:35:12.000 北京
236         3           46d54      2011-11-20 11:06:57.000 北京
236         4           46c54      2011-11-21 11:06:57.000 北京
236         5           25454      2011-11-22 11:06:57.000 北京(9 行受影响)*/
go
drop table tb

Answer 5

再增加点功能：
runid prcsid use_id prcs_time flow_id end_time
231 1 45467 2011-09-14 15:47:00 1     2011-11-07 11:06:57
231 2 67676 2011-09-14 16:35:12 1     2011-11-07 11:06:57
231 3 45454 2011-11-07 11:06:57 1     2011-11-07 11:06:57
235 1 45467 2011-11-14 15:47:00 3
235 2 67376 2011-11-14 16:35:12 3
235 3 45554 2011-11-07 11:06:57 3
235 4 45454 2011-11-07 11:06:57 3
238 1 45454 2011-11-07 11:06:27 6     
239 1 35467 2011-10-14 15:47:00 6    
239 2 67376 2011-11-14 16:35:12 6
239 3 46554 2011-11-07 11:06:57 6
239 4 46454 2011-11-07 11:06:57 6
239 5 25454 2011-11-07 11:06:57 6
236 1 35467 2011-11-17 15:47:00 7     2011-11-22 11:06:57
236 2 67376 2011-11-19 16:35:12 7     2011-11-22 11:06:57
236 3 46d54 2011-11-20 11:06:57 7     2011-11-22 11:06:57
236 4 46c54 2011-11-21 11:06:57 7     2011-11-22 11:06:57
236 5 25454 2011-11-22 11:06:57 7     2011-11-22 11:06:57
266 1 25654 2011-11-22 16:06:57 8我想查找出prcs_time在11月1日到11月30日之间的数据并且prcsid字段为1那行数据的prcs_time列也必须为11月份的那组数据，并且我还想把prcsid列单独为1出现的数据全部过滤掉，即过滤掉非重复runid列数据，并且我还想判断flow_id为：1，3，6判断为上海，flow_id为7，8判断为北京，并且判断end_time如果为空则为空的位置显示“未结束”，如果不为空则在结束时间的后面增加（已结束）标识，结果如下：runid prcsid use_id prcs_time 区域     end_time235 1 45467 2011-11-14 15:47:00 上海   (未结束) 
235 2 67376 2011-11-14 16:35:12 上海   (未结束)
235 3 45554 2011-11-07 11:06:57 上海   (未结束)
235 4 45454 2011-11-07 11:06:57 上海   (未结束)
236 1 35467 2011-11-17 15:47:00 北京  2011-11-22 11:06:57(已结束)
236 2 67376 2011-11-19 16:35:12 北京  2011-11-22 11:06:57(已结束)
236 3 46d54 2011-11-20 11:06:57 北京  2011-11-22 11:06:57(已结束)
236 4 46c54 2011-11-21 11:06:57 北京   2011-11-22 11:06:57(已结束)
236 5 25454 2011-11-22 11:06:57 北京  2011-11-22 11:06:57(已结束)
另外还想判断该表中runid为同样ID的已结束的多少和未结束的多少，比如231,236为已结束计算为已结束的为2，  235,238,239,266为没有结束即未结束的为4
请赐教，烦请贴出源码

Answer 6

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([runid] nvarchar(5),[prcsid] int,[use_id] nvarchar(19),[prcs_time] nvarchar(19),[flow_id] nvarchar(19),[end_time] Datetime)
Insert #T
select N'231 1',45467,N'2011-09-14 15:47:00',N'1',N'2011-11-07 11:06:57',null union all
select N'231',2,N'67676',N'2011-09-14 16:35:12',N'1','2011-11-07 11:06:57' union all
select N'231',3,N'45454',N'2011-11-07 11:06:57',N'1','2011-11-07 11:06:57' union all
select N'235',1,N'45467',N'2011-11-14 15:47:00',N'3',null union all
select N'235',2,N'67376',N'2011-11-14 16:35:12',N'3',null union all
select N'235',3,N'45554',N'2011-11-07 11:06:57',N'3',null union all
select N'235',4,N'45454',N'2011-11-07 11:06:57',N'3',null union all
select N'238',1,N'45454',N'2011-11-07 11:06:27',N'6',null union all
select N'239',1,N'35467',N'2011-10-14 15:47:00',N'6',null union all
select N'239',2,N'67376',N'2011-11-14 16:35:12',N'6',null union all
select N'239',3,N'46554',N'2011-11-07 11:06:57',N'6',null union all
select N'239',4,N'46454',N'2011-11-07 11:06:57',N'6',null union all
select N'239',5,N'25454',N'2011-11-07 11:06:57',N'6',null union all
select N'236',1,N'35467',N'2011-11-17 15:47:00',N'7','2011-11-22 11:06:57' union all
select N'236',2,N'67376',N'2011-11-19 16:35:12',N'7','2011-11-22 11:06:57' union all
select N'236',3,N'46d54',N'2011-11-20 11:06:57',N'7','2011-11-22 11:06:57' union all
select N'236',4,N'46c54',N'2011-11-21 11:06:57',N'7','2011-11-22 11:06:57' union all
select N'236',5,N'25454',N'2011-11-22 11:06:57',N'7','2011-11-22 11:06:57' union all
select N'266',1,N'25654',N'2011-11-22 16:06:57',N'8',null
Go
select a.[runid],a.[prcsid],a.use_id,a.prcs_time,
case when [flow_id] in(1,3,6) then N'上海' when [flow_id] in(7,8) then N'北京' end as 区域 ,
isnull(convert(varchar(19),end_time,120),N'(未结束)')+case  when end_time is not null then N'(已结束)' else '' end as end_time
from #T as a
inner join 
(Select [runid]
from #T AS a
WHERE a.[prcs_time] >= '2011-11-01' AND a.[prcs_time]<'2011-12-01'
and EXISTS(SELECT 1 FROM #T WHERE runid=a.runid having a.[prcsid]=1 or a.prcsid=max([prcsid]))
group by [runid]
having COUNT(distinct prcsid)=2
)as b on a.[runid]=b.[runid]/*
runid prcsid use_id prcs_time 区域 end_time
235 1 45467 2011-11-14 15:47:00 上海 (未结束)
235 2 67376 2011-11-14 16:35:12 上海 (未结束)
235 3 45554 2011-11-07 11:06:57 上海 (未结束)
235 4 45454 2011-11-07 11:06:57 上海 (未结束)
236 1 35467 2011-11-17 15:47:00 北京 2011-11-22 11:06:57(已结束)
236 2 67376 2011-11-19 16:35:12 北京 2011-11-22 11:06:57(已结束)
236 3 46d54 2011-11-20 11:06:57 北京 2011-11-22 11:06:57(已结束)
236 4 46c54 2011-11-21 11:06:57 北京 2011-11-22 11:06:57(已结束)
236 5 25454 2011-11-22 11:06:57 北京 2011-11-22 11:06:57(已结束)
*/

Answer 7

roy_88还没睡啊
另外还想判断该表中runid为同样ID的已结束的多少和未结束的多少，比如231,236为已结束计算为已结束的为2， 235,238,239,266为没有结束即未结束的为4

Answer 8

266 1 25654 2011-11-22 16:06:57 8
这条记录也是未结束吧--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([runid] nvarchar(5),[prcsid] int,[use_id] nvarchar(19),[prcs_time] nvarchar(19),[flow_id] nvarchar(19),[end_time] Datetime)
Insert #T
select N'231 1',45467,N'2011-09-14 15:47:00',N'1',N'2011-11-07 11:06:57',null union all
select N'231',2,N'67676',N'2011-09-14 16:35:12',N'1','2011-11-07 11:06:57' union all
select N'231',3,N'45454',N'2011-11-07 11:06:57',N'1','2011-11-07 11:06:57' union all
select N'235',1,N'45467',N'2011-11-14 15:47:00',N'3',null union all
select N'235',2,N'67376',N'2011-11-14 16:35:12',N'3',null union all
select N'235',3,N'45554',N'2011-11-07 11:06:57',N'3',null union all
select N'235',4,N'45454',N'2011-11-07 11:06:57',N'3',null union all
select N'238',1,N'45454',N'2011-11-07 11:06:27',N'6',null union all
select N'239',1,N'35467',N'2011-10-14 15:47:00',N'6',null union all
select N'239',2,N'67376',N'2011-11-14 16:35:12',N'6',null union all
select N'239',3,N'46554',N'2011-11-07 11:06:57',N'6',null union all
select N'239',4,N'46454',N'2011-11-07 11:06:57',N'6',null union all
select N'239',5,N'25454',N'2011-11-07 11:06:57',N'6',null union all
select N'236',1,N'35467',N'2011-11-17 15:47:00',N'7','2011-11-22 11:06:57' union all
select N'236',2,N'67376',N'2011-11-19 16:35:12',N'7','2011-11-22 11:06:57' union all
select N'236',3,N'46d54',N'2011-11-20 11:06:57',N'7','2011-11-22 11:06:57' union all
select N'236',4,N'46c54',N'2011-11-21 11:06:57',N'7','2011-11-22 11:06:57' union all
select N'236',5,N'25454',N'2011-11-22 11:06:57',N'7','2011-11-22 11:06:57' union all
select N'266',1,N'25654',N'2011-11-22 16:06:57',N'8',nullselect 
SUM(con) as 已结束,abs(SUM(1-con)) as 未结束
from 
(select 
min(case when [end_time] is null then 0 else 1 end) as con
from #T 
group by [runid]
)t
/*
已结束 未结束
2 5
*/

Answer 9

to roy_88
就是包括266 1 25654 2011-11-22 16:06:57 8 这条数据总共4条未结束

Answer 10

谢谢roy_88,你是对的
但我还想在每一个结束了的runid的最后一列打上“结束”表示在这里结束的 效果如下：runid    prcsid    use_id    prcs_time    区域    end_time                      结束位置
235    1    45467    2011-11-14 15:47:00    上海    (未结束)
235    2    67376    2011-11-14 16:35:12    上海    (未结束)
235    3    45554    2011-11-07 11:06:57    上海    (未结束)
235    4    45454    2011-11-07 11:06:57    上海    (未结束)                      
236    1    35467    2011-11-17 15:47:00    北京    2011-11-22 11:06:57(已结束)
236    2    67376    2011-11-19 16:35:12    北京    2011-11-22 11:06:57(已结束)
236    3    46d54    2011-11-20 11:06:57    北京    2011-11-22 11:06:57(已结束)
236    4    46c54    2011-11-21 11:06:57    北京    2011-11-22 11:06:57(已结束)
236    5    25454    2011-11-22 11:06:57    北京    2011-11-22 11:06:57(已结束)    结束
或者：runid prcsid use_id prcs_time flow_id end_time                  结束位置
231 1 45467 2011-09-14 15:47:00 1 2011-11-07 11:06:57
231 2 67676 2011-09-14 16:35:12 1 2011-11-07 11:06:57
231 3 45454 2011-11-07 11:06:57 1 2011-11-07 11:06:57           结束
235 1 45467 2011-11-14 15:47:00 3
235 2 67376 2011-11-14 16:35:12 3
235 3 45554 2011-11-07 11:06:57 3
235 4 45454 2011-11-07 11:06:57 3
238 1 45454 2011-11-07 11:06:27 6  
239 1 35467 2011-10-14 15:47:00 6  
239 2 67376 2011-11-14 16:35:12 6
239 3 46554 2011-11-07 11:06:57 6
239 4 46454 2011-11-07 11:06:57 6
239 5 25454 2011-11-07 11:06:57 6
236 1 35467 2011-11-17 15:47:00 7 2011-11-22 11:06:57
236 2 67376 2011-11-19 16:35:12 7 2011-11-22 11:06:57
236 3 46d54 2011-11-20 11:06:57 7 2011-11-22 11:06:57
236 4 46c54 2011-11-21 11:06:57 7 2011-11-22 11:06:57
236 5 25454 2011-11-22 11:06:57 7 2011-11-22 11:06:57           结束
266 1 25654 2011-11-22 16:06:57 8