找出符合条件的记录

Question

我有一张表如下，找出第一字段中不含":21"， 且在它的时间之后没有含":21"PROC_sge_execd_Crit:scrptb45:LZ:sge_execd:ITM_KLZ_Process          12/18/2014 2:12:21 PM
PROC_sge_execd_Crit:scrptb45:LZ:sge_execd:ITM_KLZ_Process:21          12/18/2014 2:22:21 PM
PROC_master_Crit:scrptb56:LZ::ITM_KLZ_Process                          12/19/2014 9:56:53 PM
PROC_htt913C717515AD49DF:scrpt10:LZ:httpd2-prefork:ITM_KLZ_Process  12/19/2014 9:57:54 PM
PROC_activemq_Crit:scrptb01:LZ::ITM_KLZ_Process                  12/19/2014 10:00:16 PM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21                                  12/20/2014 1:11:21 PM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21                                   12/20/2014 7:26:20 PM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21                                   12/20/2014 10:41:20 PM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21                                   12/21/2014 6:11:19 AM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21                                    12/21/2014 12:41:18 PM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk                                             12/23/2014 6:26:13 AM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk                                            12/23/2014 9:03:55 AM
Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21                                    12/23/2014 9:11:12 AM
找出下面三条记录：
PROC_master_Crit:scrptb56:LZ::ITM_KLZ_Process                                                 12/19/2014 9:56:53 PM
PROC_htt913C717515AD49DF:scrpt10:LZ:httpd2-prefork:ITM_KLZ_Process   12/19/2014 9:57:54 PM
PROC_activemq_Crit:scrptb01:LZ::ITM_KLZ_Process                                             12/19/2014 10:00:16 PM

Answer 1

直接用charindex('21',[列]) ，性能很差的哦

Answer 2

不明白，求具体的sql, 谢谢

Answer 3

不是，
如：
最后一条Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk 之后有一条Filesystem_Crit:scrpt10:LZ::ITM_KLZ_Disk:21 , 这不是我要的，
最后一条PROC_master_Crit:scrptb56:LZ::ITM_KLZ_Process 之后没有PROC_master_Crit:scrptb56:LZ::ITM_KLZ_Process:21, 这是我要的

Answer 4

;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY time1)RN
,*
FROM TB
)
SELECT * FROM CTE T1
WHERE col NOT LIKE '%:21'
AND NOT EXISTS
(SELECT 1 FROM CTE T2 WHERE T1.RN+1=T2.RN
AND T2.col LIKE '%:21')

Answer 5

SELECT * FROM TB T1
WHERE col NOT LIKE'%:21'
AND NOT EXISTS
(SELECT 1 FROM TB T2 WHERE T1.col+':21'=T2.col AND T1.time1<T2.time1)试试这个

Answer 6

谢谢 ky_min，这是给我做好的圣诞礼物, 这个非常好。
祝大家圣诞、新年快乐。