表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判断为北京，并且判断end_time如果为空则为空的位置显示“未结束”，如果不为空则在结束时间的后面增加（已结束）标识，我还想在每一个结束了的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(已结束) 结束
------解决方案--------------------
- SQL code
if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([runid] nvarchar(5),[prcsid] int,[use_id] nvarchar(19),[prcs_time] nvarchar(19),[flow_id] nvarchar(19),[end_time] Datetime)Insert #Tselect N'231 1',45467,N'2011-09-14 15:47:00',N'1',N'2011-11-07 11:06:57',null union allselect N'231',2,N'67676',N'2011-09-14 16:35:12',N'1','2011-11-07 11:06:57' union allselect N'231',3,N'45454',N'2011-11-07 11:06:57',N'1','2011-11-07 11:06:57' union allselect N'235',1,N'45467',N'2011-11-14 15:47:00',N'3',null union allselect N'235',2,N'67376',N'2011-11-14 16:35:12',N'3',null union allselect N'235',3,N'45554',N'2011-11-07 11:06:57',N'3',null union allselect N'235',4,N'45454',N'2011-11-07 11:06:57',N'3',null union allselect N'238',1,N'45454',N'2011-11-07 11:06:27',N'6',null union allselect N'239',1,N'35467',N'2011-10-14 15:47:00',N'6',null union allselect N'239',2,N'67376',N'2011-11-14 16:35:12',N'6',null union allselect N'239',3,N'46554',N'2011-11-07 11:06:57',N'6',null union allselect N'239',4,N'46454',N'2011-11-07 11:06:57',N'6',null union allselect N'239',5,N'25454',N'2011-11-07 11:06:57',N'6',null union allselect N'236',1,N'35467',N'2011-11-17 15:47:00',N'7','2011-11-22 11:06:57' union allselect N'236',2,N'67376',N'2011-11-19 16:35:12',N'7','2011-11-22 11:06:57' union allselect N'236',3,N'46d54',N'2011-11-20 11:06:57',N'7','2011-11-22 11:06:57' union allselect N'236',4,N'46c54',N'2011-11-21 11:06:57',N'7','2011-11-22 11:06:57' union allselect N'236',5,N'25454',N'2011-11-22 11:06:57',N'7','2011-11-22 11:06:57' union allselect N'266',1,N'25654',N'2011-11-22 16:06:57',N'8',nullGoselect 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(nvarchar(19),end_time,120),N'(未结束)')+case when end_time is not null then N'(已结束)' else '' end as end_time,CASE WHEN a.prcsid=b.[MaxPrcsid] AND end_time IS NOT null THEN N'结束' ELSE '' END AS 结束位置from #T as ainner join (Select [runid],MAX([prcsid]) AS [MaxPrcsid]from #T AS aWHERE 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(已结束) 结束*/