这是我的sql语句。
create table #t(temp varchar(1000))
insert into #t Exec xp_cmdshell 'type c:\111.TXT'
select temp from #t where temp like '%CLOY_PROMO_ITEMS%' or ISDATE(temp)=1
drop table #t得出的结果如下,
8:42:22 12/06/11
8:42:29 12/06/11
8:43:22 12/06/11
8:43:29 12/06/11
8:43:29 [CLOY_PROMO_ITEMS].PROMO_ID = 7226
9:46:24 12/07/11
9:46:30 12/07/11
9:46:30 [CLOY_PROMO_ITEMS].PROMO_ID = 7227 如何获取[CLOY_PROMO_ITEMS]关键字 前面一条日期了,包含关键字本条记录。
create table #t(temp varchar(1000))
insert into #t Exec xp_cmdshell 'type c:\111.TXT'
select temp from #t where temp like '%CLOY_PROMO_ITEMS%' or ISDATE(temp)=1
drop table #t得出的结果如下,
8:42:22 12/06/11
8:42:29 12/06/11
8:43:22 12/06/11
8:43:29 12/06/11
8:43:29 [CLOY_PROMO_ITEMS].PROMO_ID = 7226
9:46:24 12/07/11
9:46:30 12/07/11
9:46:30 [CLOY_PROMO_ITEMS].PROMO_ID = 7227 如何获取[CLOY_PROMO_ITEMS]关键字 前面一条日期了,包含关键字本条记录。
然后得到 第n和第n-1条?
create table #t(temp varchar(1000))
insert into #t Exec xp_cmdshell 'type c:\111.TXT'
select temp from #t where temp like '%CLOY_PROMO_ITEMS%' or ISDATE(temp)=1;with cte as
(
select rn=row_number() over (order by getdate()),*
from #t
)select a.temp,b.temp as date
from cte a join (select rn,temp from cte where temp like '%[CLOY_PROMO_ITEMS]%') b
on a.rn = b.rn - 1drop table #t
create table #t(temp varchar(1000))
insert into #t Exec xp_cmdshell 'type c:\111.TXT'
select temp from #t where temp like '%CLOY_PROMO_ITEMS%' or ISDATE(temp)=1select rn=identity(int,1,1),*
into #cte
from #tselect a.temp,b.temp as date
from #cte a join (select rn,temp from #cte where temp like '%[CLOY_PROMO_ITEMS]%') b
on a.rn = b.rn - 1drop table #t,#cte
你好,这个得到的结果好像有点不对,,#cte临时表两个字段得出的结果如下。
temp结果:除了关键字以外所有的数据
data结果:出了日期以外所有的数据。
8:43:29 [CLOY_PROMO_ITEMS].PROMO_ID = 7226 9:46:30 12/07/11
9:46:30 [CLOY_PROMO_ITEMS].PROMO_ID = 7227
WHERE temp NOT LIKE '%CLOY_PROMO_ITEMS%'--筛选关键字记录
AND temp IN (SELECT LEFT(temp, 6) FROM #t WHERE temp LIKE '%CLOY_PROMO_ITEMS%')--筛选不是关键字日期记录
你好,这样得出的结果还是只得出了关键字这一行的记录,并没有包括日期。
我需要得到的结果如下: 8:43:29 12/06/11
8:43:29 [CLOY_PROMO_ITEMS].PROMO_ID = 7226 9:46:30 12/07/11
9:46:30 [CLOY_PROMO_ITEMS].PROMO_ID = 7227
into #cte
from #tselect a.temp
from #cte a join (select rn,temp from #cte where temp like '%[CLOY_PROMO_ITEMS]%') b
on a.rn IN (b.rn, b.rn - 1)drop table #t,#cte
insert into #t Exec xp_cmdshell 'type c:\111.TXT' select rn=identity(int,1,1),*
into #cte
from #t select a.temp
from #cte a join (select rn,temp from #cte where temp like '%[CLOY_PROMO_ITEMS]%') b
on a.rn IN (b.rn, b.rn - 1)drop table #t,#cte你好,这样得出的结果还是不正确。
from #cte a join (select rn,temp from #cte where temp like '%[CLOY_PROMO_ITEMS]%') b
on a.rn IN (b.rn, b.rn - 1)
order by rn