create table t_panda(stext varchar(500))
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'--select * from t_panda上面的数据是从txt文件中导的,我现在要变成下面的数据1.00:00:02,000 --> 00:00:08,010.<font color=#38B0DE>功夫熊猫</font>
2.00:00:10,664 --> 00:00:14,096.传说中有个传奇侠客
3.00:00:14,105 --> 00:00:17,818.他的武功出神入化
4.00:00:18,431 --> 00:00:22,503.浪迹江湖 一路行侠仗义
5.00:00:32,602 --> 00:00:34,433.小样儿你喜欢嚼东西
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'--select * from t_panda上面的数据是从txt文件中导的,我现在要变成下面的数据1.00:00:02,000 --> 00:00:08,010.<font color=#38B0DE>功夫熊猫</font>
2.00:00:10,664 --> 00:00:14,096.传说中有个传奇侠客
3.00:00:14,105 --> 00:00:17,818.他的武功出神入化
4.00:00:18,431 --> 00:00:22,503.浪迹江湖 一路行侠仗义
5.00:00:32,602 --> 00:00:34,433.小样儿你喜欢嚼东西
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'
go
;with fc as
(
select *,row_number() over(order by getdate()) idx,(row_number() over(order by getdate())+3)/4 gid from t_panda
)
select a.stext + '.' + b.stext + ' ' + c.stext from fc a,fc b,fc c
where a.gid=b.gid and b.gid=c.gid and a.idx%4=1 and b.idx%4=2 and c.idx%4=3
/*
1.00:00:02,000 --> 00:00:08,010 <font color=#38B0DE>功夫熊猫</font>
2.00:00:10,664 --> 00:00:14,096 传说中有个传奇侠客
3.00:00:14,105 --> 00:00:17,818 他的武功出神入化
4.00:00:18,431 --> 00:00:22,503 浪迹江湖 一路行侠仗义
5.00:00:32,602 --> 00:00:34,433 小样儿你喜欢嚼东西
*/
go
drop table t_panda
go
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'SELECT ID=IDENTITY(INT,1,1),* INTO # FROM t_pandaSELECT MAX(CASE WHEN ID%4-1=0 THEN stext END),
MAX(CASE WHEN ID%4-1=1 THEN stext END),
MAX(CASE WHEN ID%4-1=2 THEN stext END)
FROM #
GROUP BY ID/4DROP TABLE t_panda,#/*
1 00:00:02,000 --> 00:00:08,010 <font color=#38B0DE>功夫熊猫</font>
2 00:00:10,664 --> 00:00:14,096 传说中有个传奇侠客
3 00:00:14,105 --> 00:00:17,818 他的武功出神入化
4 00:00:18,431 --> 00:00:22,503 浪迹江湖 一路行侠仗义
5 00:00:32,602 --> 00:00:34,433 小样儿你喜欢嚼东西
*/
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'
goselect idx=identity(int),* into #fc from t_pandaselect a.stext + '.' + b.stext + ' ' + c.stext from #fc a,#fc b,#fc c
where (a.idx+3)/4= (b.idx+3)/4 and (a.idx+3)/4= (c.idx+3)/4 and a.idx%4=1 and b.idx%4=2 and c.idx%4=3
/*
1.00:00:02,000 --> 00:00:08,010 <font color=#38B0DE>功夫熊猫</font>
2.00:00:10,664 --> 00:00:14,096 传说中有个传奇侠客
3.00:00:14,105 --> 00:00:17,818 他的武功出神入化
4.00:00:18,431 --> 00:00:22,503 浪迹江湖 一路行侠仗义
5.00:00:32,602 --> 00:00:34,433 小样儿你喜欢嚼东西
*/
go
drop table t_panda,#fc
go
继续,2楼写得感觉好难
00:00:33,840 --> 00:00:36,959
Legend tells of a legendary warrior,2
00:00:36,960 --> 00:00:40,040
whose kung fu skills
were the stuff of legend.3
00:00:41,060 --> 00:00:44,780
He travelled the land
in search of worthy foes.4
00:00:54,940 --> 00:00:56,540
I see you like to chew.5
00:00:56,580 --> 00:00:59,640
Maybe you should on my fist.6
00:01:00,220 --> 00:01:03,799
The warrior said anything
for his mouth was full.7
00:01:03,800 --> 00:01:05,860
Then he swallowed.
梁:这样会有一些问题,我还有这样得数据。
3楼..SQL2000的..很简短的代码..
2000如果<8000也可以整declare @stext varchar(max)
set @stext=''
select @stext=@stext+case when stext='' then ''',1,1,'''') union all select stuff('''
else '.'+stext end
from t_panda
select @stext='select stuff('''+@stext+''',1,1,'''')'
exec(@stext)1.00:00:02,000 --> 00:00:08,010.<font color=#38B0DE>功夫熊貓</font>
2.00:00:10,664 --> 00:00:14,096.傳說中俠客
3.00:00:14,105 --> 00:00:17,818.武功出神入化
4.00:00:18,431 --> 00:00:22,503.浪跡江湖
5.00:00:32,602 --> 00:00:34,433.一樣喜歡吃東西
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select '浪迹江湖 一路行侠仗义'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'
go
;with fc as
(
select *,row_number() over(order by getdate()) idx from t_panda where stext!=''
),
fc1 as
(
select *,gid=(select max(idx) from (select idx from fc where idx<=a.idx and isnumeric(stext)=1 ) b) from fc a
)
select v from
(select distinct gid from fc1) b
cross apply
(select x=(select stext from fc1 where gid=b.gid for xml path('r'),type)) a
cross apply
(select v=a.x.query('for $r in //r/stext return xs:string($r)').value('.','varchar(max)')) c/*
1 00:00:02,000 --> 00:00:08,010 <font color=#38B0DE>功夫熊猫</font>
2 00:00:10,664 --> 00:00:14,096 传说中有个传奇侠客
3 00:00:14,105 --> 00:00:17,818 他的武功出神入化
4 00:00:18,431 --> 00:00:22,503 浪迹江湖 一路行侠仗义 浪迹江湖 一路行侠仗义 浪迹江湖 一路行侠仗义
5 00:00:32,602 --> 00:00:34,433 小样儿你喜欢嚼东西
*/go
drop table t_panda
go
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select '浪迹江湖 一路行侠仗义'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'
go
;with fc as
(
select *,row_number() over(order by getdate()) idx from t_panda where stext!=''
),
fc1 as
(
select *,gid=(select max(idx) from (select idx from fc where idx<=a.idx and isnumeric(stext)=1 ) b) from fc a
)
select stuff(x.value('r[1]','varchar(max)'),1,1,'') from
(select distinct gid from fc1) b
cross apply
(select x=(select '.' + stext from fc1 where gid=b.gid for xml path(''),root('r'),type)) a
/*
1.00:00:02,000 --> 00:00:08,010.<font color=#38B0DE>功夫熊猫</font>
2.00:00:10,664 --> 00:00:14,096.传说中有个传奇侠客
3.00:00:14,105 --> 00:00:17,818.他的武功出神入化
4.00:00:18,431 --> 00:00:22,503.浪迹江湖 一路行侠仗义.浪迹江湖 一路行侠仗义.浪迹江湖 一路行侠仗义
5.00:00:32,602 --> 00:00:34,433.小样儿你喜欢嚼东西
*/go
drop table t_panda
go
;with fc as
(
select *,row_number() over(order by getdate()) idx from t_panda where stext!=''
),
fc1 as
(
select *,gid=(select max(idx) from (select idx from fc where idx<=a.idx and isnumeric(stext)=1 ) b) from fc a
)
select v from
(select distinct gid from fc1) b
cross apply
(select x=(select stext from fc1 where gid=b.gid for xml path('r'),type)) a
cross apply
(select v=a.x.query('for $r in //r/stext return xs:string($r)').value('.','varchar(max)')) c为了这个把连接从2000改为了2005。运行了7分钟没反应,原来数据中有这个,看图片,现在都不知道怎么把这些黑点删除,
因为一插到数据库中就成了一行
强悍的硬盘图,你得先上传到CSDN上才行,呵呵
create table t_panda(stext varchar(500))
insert into t_panda
select'1'
union all select '00:00:02,000 --> 00:00:08,010'
union all select '<font color=#38B0DE>功夫熊猫</font>'
union all select ''
union all select '2'
union all select '00:00:10,664 --> 00:00:14,096'
union all select '传说中有个传奇侠客'
union all select ''
union all select '3'
union all select '00:00:14,105 --> 00:00:17,818'
union all select '他的武功出神入化'
union all select ''
union all select '4'
union all select '00:00:18,431 --> 00:00:22,503'
union all select '浪迹江湖 一路行侠仗义'
union all select '浪迹江湖 一路行侠仗义'
union all select '浪迹江湖 一路行侠仗义'
union all select ''
union all select '5'
union all select '00:00:32,602 --> 00:00:34,433'
union all select '小样儿你喜欢嚼东西'
go
alter table t_panda add idx int identity(1,1)
alter table t_panda add gid int null
go
delete t_panda where stext=''
update a set a.gid = (
select max(stext) from
(select stext from t_panda where stext!='' and isnumeric(stext)=1 and idx<=a.idx) a
)
from t_panda a
select * from t_panda
go
create function joinStr(@gid int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r = isnull(@r + '.','') + stext from t_panda where gid=@gid
return @r
end
go
select dbo.joinStr(gid) from t_panda group by gid
/*
1.00:00:02,000 --> 00:00:08,010.<font color=#38B0DE>功夫熊猫</font>
2.00:00:10,664 --> 00:00:14,096.传说中有个传奇侠客
3.00:00:14,105 --> 00:00:17,818.他的武功出神入化
4.00:00:18,431 --> 00:00:22,503.浪迹江湖 一路行侠仗义.浪迹江湖 一路行侠仗义.浪迹江湖 一路行侠仗义
5.00:00:32,602 --> 00:00:34,433.小样儿你喜欢嚼东西
*/
go
drop table t_panda
drop function joinStr
go
你帮人帮到底吧,我现在把空值的都删了,因为有空1,2,3行的都有
成了这样的数据。94
00:06:36,364 --> 00:06:38,820
平先生 火速飞去啸岗监狱
95
00:06:38,832 --> 00:06:41,654
叫他们把狱守数量加倍
把武器加倍
96
00:06:41,664 --> 00:06:42,814
把一切都加倍
把它们帮我搞好就可以啦。搞成3行
一行id,一行时间段,一行 文本内容?sql2000 or sql2005?
sql2000 or sql2005?都可以,什么方法都可以。
我已经把空值都删了。94 00:06:36,364 --> 00:06:38,820 平先生 火速飞去啸岗监狱
95 00:06:38,832 --> 00:06:41,654 们把狱守数量加倍 把武器加倍
96 00:06:41,664 --> 00:06:42,814 把一切都加倍