！！！！！500 分 求一句SQL！！！！！（顶贴有分，解决问题另送500！！）
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @aimstr varchar(8000)
set @str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005<1004>~~~~~~~~~~~~~~~~<1005>1.005473262e+005<1006>1.006921238e+005<1007>1.007874794e+004<1008>1.008783206e+005<1009>1.009795125e+005<1010>1.010351993e+005<1011>1.011101677e+005<1012>1.012274451e+005<1013>1.013116637e+005<1014>1.014803895e+005<1015>1.015176963e+005<1016>1.016797904e+005<1017>1.017756190e+005<1018>1.018285658e+004<1019>1.019068725e+004<1020>1.020190971e+005<1021>1.021645718e+005<1022>1.022370188e+005<1023>1.023530449e+005<1024>1.024859832e+005<1025>1.025633568e+005<1026>1.026423250e+005<1027>1.027491443e+005<1028>1.028643896e+005<1029>1.029326061e+005<1030>1.030949363e+005<1031>1.031428525e+004<1032>1.032861365e+005<1033>1.033885098e+005<1034>1.034521993e+005<1035>1.035871819e+005<1036>1.036137106e+005<1037>1.037711253e+005<1038>1.038528526e+005<1039>1.039517296e+005<1040>1.040538440e+005<1041>1.041235082e+005<1042>1.042013249e+004<1043>1.043338208e+005<1044>1.044458341e+005<1045>1.045618668e+005<1046>1.046829918e+005<1047>1.047558578e+005<1048>1.048505013e+005<1049>1.049266989e+005<1050>1.050922411e+005'
set @str2= '<1001>~~~~~~~~~~~~~~~~<1002>~~~~~~~~~~~~~~~~<1003>~~~~~~~~~~~~~~~~<1004>1.004113740e+005<1005>~~~~~~~~~~~~~~~~<1006>~~~~~~~~~~~~~~~~<1007>~~~~~~~~~~~~~~~~<1008>~~~~~~~~~~~~~~~~<1009>~~~~~~~~~~~~~~~~<1010>~~~~~~~~~~~~~~~~<1011>~~~~~~~~~~~~~~~~<1012>~~~~~~~~~~~~~~~~<1013>~~~~~~~~~~~~~~~~<1014>~~~~~~~~~~~~~~~~<1015>~~~~~~~~~~~~~~~~<1016>~~~~~~~~~~~~~~~~<1017>~~~~~~~~~~~~~~~~<1018>~~~~~~~~~~~~~~~~<1019>~~~~~~~~~~~~~~~~<1020>~~~~~~~~~~~~~~~~<1021>~~~~~~~~~~~~~~~~<1022>~~~~~~~~~~~~~~~~<1023>~~~~~~~~~~~~~~~~<1024>~~~~~~~~~~~~~~~~<1025>~~~~~~~~~~~~~~~~<1026>~~~~~~~~~~~~~~~~<1027>~~~~~~~~~~~~~~~~<1028>~~~~~~~~~~~~~~~~<1029>~~~~~~~~~~~~~~~~<1030>~~~~~~~~~~~~~~~~<1031>~~~~~~~~~~~~~~~~<1032>~~~~~~~~~~~~~~~~<1033>~~~~~~~~~~~~~~~~<1034>~~~~~~~~~~~~~~~~<1035>~~~~~~~~~~~~~~~~<1036>~~~~~~~~~~~~~~~~<1037>~~~~~~~~~~~~~~~~<1038>~~~~~~~~~~~~~~~~<1039>~~~~~~~~~~~~~~~~<1040>~~~~~~~~~~~~~~~~<1041>~~~~~~~~~~~~~~~~<1042>~~~~~~~~~~~~~~~~<1043>~~~~~~~~~~~~~~~~<1044>~~~~~~~~~~~~~~~~<1045>~~~~~~~~~~~~~~~~<1046>~~~~~~~~~~~~~~~~<1047>~~~~~~~~~~~~~~~~<1048>~~~~~~~~~~~~~~~~<1049>~~~~~~~~~~~~~~~~<1050>~~~~~~~~~~~~~~~~'
set @aimstr=''
declare @i bigint
declare @num bigint
declare @len bigint
declare @char1 int
declare @char2 int
set @len=len(@str1)
set @i=1
set @num=1
while(@num<10000)
begin
while(@i<@len)
begin
set @char1 =ascii( substring(@str1,@i,1))
set @char2 =ascii( substring(@str2,@i,1))
set @aimstr=@aimstr+(case when @char1<@char2 then substring(@str1,@i,1) else substring(@str2,@i,1) end)
set @i=@i+1
end
set @num=@num+1
end
print @aimstr
在查询分析器中运行，得到@str1和@str2中每一个ASCII比较大的字符
这样做，效率很差，有没有好的方法，用一句SQL解决？
或者其他效率比较高的方案。
先谢了！
问题点数：100、回复次数：61Top
1 楼librastar2001（天平）回复于 2004-12-03 11:58:51 得分 0
MSN:liuyt1017@hotmail.comTop
2 楼pweixing（幸运星）回复于 2004-12-03 11:58:58 得分 2
晕！
能否直接把表给我们看看啊！看你的过程看的头疼！Top
3 楼yyhyy23（只爱猪猪）回复于 2004-12-03 12:18:14 得分 2
upTop
4 楼didoleo（冷月无声）回复于 2004-12-03 12:21:24 得分 2
upTop
5 楼yelook（香槟酒）回复于 2004-12-03 12:22:05 得分 2
学习...关注...Top
6 楼YangYuWeb（飘邈...）回复于 2004-12-03 12:23:12 得分 2
帮顶Top
7 楼lionlzy（【风云】）回复于 2004-12-03 12:24:22 得分 2
看了头疼Top
8 楼qingbai（一松傲雪）回复于 2004-12-03 12:27:03 得分 2
把你要解决的问题或要实现的功能说清楚，而不是贴一大堆代码！Top
9 楼pbsql（风云）回复于 2004-12-03 12:27:38 得分 10
直接比较就行了，不要转换成ASC码：
set @aimstr=@aimstr
+case when substring(@str1,@i,1)<substring(@str2,@i,1)
then substring(@str1,@i,1)
else substring(@str2,@i,1) end
Top
10 楼tddw（承接各种项目）回复于 2004-12-03 12:30:32 得分 0
写个自定义函数，功能求一串字符中ascll最大的一个字符。
然后一句话就搞定了。Top
11 楼tddw（承接各种项目）回复于 2004-12-03 12:35:09 得分 2
另，你的字符串中全是英文吗？如果全是英文或者标点，我到是有一个办法Top
12 楼librastar2001（天平）回复于 2004-12-03 13:58:43 得分 0
不用循环怎么做？？？Top
13 楼librastar2001（天平）回复于 2004-12-03 14:04:28 得分 0
我做了循环1000次，用了7秒，但是只允许在1秒内。。。Top
14 楼tddw（承接各种项目）回复于 2004-12-03 14:08:23 得分 5
我讲一下我的思路了，代码你自己写了。呵呵，我现在比较忙
如果你的字符串中全是英文或者标点呢，循环肯定是用循环的，不过，不用那么麻烦。
跑来先用PATINDEX 查一下字符串中是否有ascll码＝255的值，如果有的话，那么，最大字符就是ascll为255的字符，如果没有，再查254的，一直查下去，200多次就搞定。且速度应该非常快的。
不知道行不行？不行不要骂我哦Top
15 楼librastar2001（天平）回复于 2004-12-03 14:11:16 得分 0
问题是，我有1000条以上这样的记录
如果每一条都需要循环的话，不是要200000次？？
晕倒~~~~~~~~~Top
16 楼huoyou342（飞雪零飘）回复于 2004-12-03 14:15:25 得分 2
顶Top
17 楼vinsonshen（为了明天）回复于 2004-12-03 14:15:25 得分 2
帮顶下先~~Top
18 楼librastar2001（天平）回复于 2004-12-03 14:17:26 得分 0
说明一下我的问题
条件
@str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005'
@str2= '<1001>~~~~~~~~~~~~~~~~<1002>1.003440308e+005<1003>~~~~~~~~~~~~~~~~'
结果
@str='<1001>1.001150156e+005<1002>1.003440308e+005<1003>1.003440308e+005'
Top
19 楼jiang130（Hong）回复于 2004-12-03 14:17:36 得分 2
upTop
20 楼txlicenhe（马可）回复于 2004-12-03 14:33:06 得分 0
如果楼主的数据真的那么有规律的话倒是可以如下处理:
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @s varchar(8000)
set @str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005'
set @str2= '<1001>~~~~~~~~~~~~~~~~<1002>1.003440308e+005<1003>~~~~~~~~~~~~~~~~'
declare @s1 varchar(22),@s2 varchar(22)
set @s = ''
while charindex('<',@str1) > 0
begin
set @s1 = substring(@str1,charindex('<',@str1),22)
set @s2 = substring(@str2,charindex('<',@str2),22)
set @s = @s + (case when @s1 > @s2 then @s1 else @s2 end)
set @str1 = right(@str1,len(@str1) - 22)
set @str2 = right(@str2,len(@str2) - 22)
end
select @s
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<1001>1.001150156e+005<1002>1.003440308e+005<1003>1.003440308e+005
（所影响的行数为 1 行）
*/
Top
21 楼lmj2003（雁不归）回复于 2004-12-03 14:37:09 得分 2
顶，学习Top
22 楼lh1979（rocket）回复于 2004-12-03 14:39:56 得分 2
乖，看了头晕
你说“得到@str1和@str2中每一个ASCII比较大的字符”
大于多少的才叫比较大的字符阿Top
23 楼librastar2001（天平）回复于 2004-12-03 14:43:31 得分 0
就是比较@str1和@str2中的每一个字符的ASCII码，比较大的放入结果字符串Top
24 楼freeheart1977（逍遥的心）回复于 2004-12-03 14:46:07 得分 2
晕Top
25 楼txlicenhe（马可）回复于 2004-12-03 15:21:54 得分 0
总算弄了一个不需循环的。
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @s varchar(8000)
set @str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005'
set @str2= '<1001>~~~~~~~~~~~~~~~~<1002>1.003440308e+005<1003>~~~~~~~~~~~~~~~~'
select id as id1,substring(@str1,id,1) as s1
into #tmp1
from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <= len(@str1)
order by id
select id as id2,substring(@str2,id,1) as s2
into #tmp2
from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <= len(@str2)
order by id
declare @str varchar(8000)
set @str=''
select @str=@str+(case when s1 > s2 then s1 else s2 end) from (select * from #tmp1 full join #tmp2 on id1 = id2
) aa
set @str=right(@str,len(@str)-1)
select @str
drop table #tmp1,#tmp2
/*
（所影响的行数为 66 行）
（所影响的行数为 66 行）
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1001>1~001150156e+005<1002>1~003440308e+005<1003>1~003440308e+005
（所影响的行数为 1 行）
*/Top
26 楼librastar2001（天平）回复于 2004-12-03 15:22:02 得分 0
晕
Top
27 楼txlicenhe（马可）回复于 2004-12-03 15:23:08 得分 0
奇怪小数点怎么变成 ~ 了Top
28 楼txlicenhe（马可）回复于 2004-12-03 15:24:45 得分 0
哦，小数点的ASCII码本来就本~的ASCII码小
Top
29 楼txlicenhe（马可）回复于 2004-12-03 15:25:12 得分 0
select ascii('.'),ascii('~')
/*
----------- -----------
46 126
（所影响的行数为 1 行）
*/Top
30 楼yelook（香槟酒）回复于 2004-12-03 15:26:52 得分 2
抗议!为什么这个也要放到sql中来写啊:)Top
31 楼txlicenhe（马可）回复于 2004-12-03 15:27:21 得分 0
把小数点和~处理一下：
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @s varchar(8000)
set @str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005'
set @str2= '<1001>~~~~~~~~~~~~~~~~<1002>1.003440308e+005<1003>~~~~~~~~~~~~~~~~'
select id as id1,substring(@str1,id,1) as s1
into #tmp1
from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <= len(@str1)
order by id
select id as id2,substring(@str2,id,1) as s2
into #tmp2
from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <= len(@str2)
order by id
declare @str varchar(8000)
set @str=''
select @str=@str+(case when s1>s2 or (s1 = '.' and s2 = '~') then s1 else s2 end) from (select * from #tmp1 full join #tmp2 on id1 = id2
) aa
set @str=right(@str,len(@str)-1)
select @str
drop table #tmp1,#tmp2
/*
（所影响的行数为 66 行）
（所影响的行数为 66 行）
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1001>1.001150156e+005<1002>1~003440308e+005<1003>1.003440308e+005
（所影响的行数为 1 行）
*/Top
32 楼librastar2001（天平）回复于 2004-12-03 15:28:35 得分 0
这个测试了一下速度比循环更慢了。
前面那个还可以，快多了。。。：）Top
33 楼txlicenhe（马可）回复于 2004-12-03 15:29:11 得分 0
楼主多用一点数据试试，我试了最开始给出的数据也不到1秒。
set @str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005<1004>~~~~~~~~~~~~~~~~<1005>1.005473262e+005<1006>1.006921238e+005<1007>1.007874794e+004<1008>1.008783206e+005<1009>1.009795125e+005<1010>1.010351993e+005<1011>1.011101677e+005<1012>1.012274451e+005<1013>1.013116637e+005<1014>1.014803895e+005<1015>1.015176963e+005<1016>1.016797904e+005<1017>1.017756190e+005<1018>1.018285658e+004<1019>1.019068725e+004<1020>1.020190971e+005<1021>1.021645718e+005<1022>1.022370188e+005<1023>1.023530449e+005<1024>1.024859832e+005<1025>1.025633568e+005<1026>1.026423250e+005<1027>1.027491443e+005<1028>1.028643896e+005<1029>1.029326061e+005<1030>1.030949363e+005<1031>1.031428525e+004<1032>1.032861365e+005<1033>1.033885098e+005<1034>1.034521993e+005<1035>1.035871819e+005<1036>1.036137106e+005<1037>1.037711253e+005<1038>1.038528526e+005<1039>1.039517296e+005<1040>1.040538440e+005<1041>1.041235082e+005<1042>1.042013249e+004<1043>1.043338208e+005<1044>1.044458341e+005<1045>1.045618668e+005<1046>1.046829918e+005<1047>1.047558578e+005<1048>1.048505013e+005<1049>1.049266989e+005<1050>1.050922411e+005'
set @str2= '<1001>~~~~~~~~~~~~~~~~<1002>~~~~~~~~~~~~~~~~<1003>~~~~~~~~~~~~~~~~<1004>1.004113740e+005<1005>~~~~~~~~~~~~~~~~<1006>~~~~~~~~~~~~~~~~<1007>~~~~~~~~~~~~~~~~<1008>~~~~~~~~~~~~~~~~<1009>~~~~~~~~~~~~~~~~<1010>~~~~~~~~~~~~~~~~<1011>~~~~~~~~~~~~~~~~<1012>~~~~~~~~~~~~~~~~<1013>~~~~~~~~~~~~~~~~<1014>~~~~~~~~~~~~~~~~<1015>~~~~~~~~~~~~~~~~<1016>~~~~~~~~~~~~~~~~<1017>~~~~~~~~~~~~~~~~<1018>~~~~~~~~~~~~~~~~<1019>~~~~~~~~~~~~~~~~<1020>~~~~~~~~~~~~~~~~<1021>~~~~~~~~~~~~~~~~<1022>~~~~~~~~~~~~~~~~<1023>~~~~~~~~~~~~~~~~<1024>~~~~~~~~~~~~~~~~<1025>~~~~~~~~~~~~~~~~<1026>~~~~~~~~~~~~~~~~<1027>~~~~~~~~~~~~~~~~<1028>~~~~~~~~~~~~~~~~<1029>~~~~~~~~~~~~~~~~<1030>~~~~~~~~~~~~~~~~<1031>~~~~~~~~~~~~~~~~<1032>~~~~~~~~~~~~~~~~<1033>~~~~~~~~~~~~~~~~<1034>~~~~~~~~~~~~~~~~<1035>~~~~~~~~~~~~~~~~<1036>~~~~~~~~~~~~~~~~<1037>~~~~~~~~~~~~~~~~<1038>~~~~~~~~~~~~~~~~<1039>~~~~~~~~~~~~~~~~<1040>~~~~~~~~~~~~~~~~<1041>~~~~~~~~~~~~~~~~<1042>~~~~~~~~~~~~~~~~<1043>~~~~~~~~~~~~~~~~<1044>~~~~~~~~~~~~~~~~<1045>~~~~~~~~~~~~~~~~<1046>~~~~~~~~~~~~~~~~<1047>~~~~~~~~~~~~~~~~<1048>~~~~~~~~~~~~~~~~<1049>~~~~~~~~~~~~~~~~<1050>~~~~~~~~~~~~~~~~'
Top
34 楼sxzqlzx（）回复于 2004-12-03 15:30:28 得分 2
我记得sql的块大小为8192B,只能存8096个B的内容Top
35 楼txlicenhe（马可）回复于 2004-12-03 15:35:23 得分 0
declare @date1 datetime
select @date1 = getdate()
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @s varchar(8000)
set @str1= '<1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005<1004>~~~~~~~~~~~~~~~~<1005>1.005473262e+005<1006>1.006921238e+005<1007>1.007874794e+004<1008>1.008783206e+005<1009>1.009795125e+005<1010>1.010351993e+005<1011>1.011101677e+005<1012>1.012274451e+005<1013>1.013116637e+005<1014>1.014803895e+005<1015>1.015176963e+005<1016>1.016797904e+005<1017>1.017756190e+005<1018>1.018285658e+004<1019>1.019068725e+004<1020>1.020190971e+005<1021>1.021645718e+005<1022>1.022370188e+005<1023>1.023530449e+005<1024>1.024859832e+005<1025>1.025633568e+005<1026>1.026423250e+005<1027>1.027491443e+005<1028>1.028643896e+005<1029>1.029326061e+005<1030>1.030949363e+005<1031>1.031428525e+004<1032>1.032861365e+005<1033>1.033885098e+005<1034>1.034521993e+005<1035>1.035871819e+005<1036>1.036137106e+005<1037>1.037711253e+005<1038>1.038528526e+005<1039>1.039517296e+005<1040>1.040538440e+005<1041>1.041235082e+005<1042>1.042013249e+004<1043>1.043338208e+005<1044>1.044458341e+005<1045>1.045618668e+005<1046>1.046829918e+005<1047>1.047558578e+005<1048>1.048505013e+005<1049>1.049266989e+005<1050>1.050922411e+005'
set @str2= '<1001>~~~~~~~~~~~~~~~~<1002>~~~~~~~~~~~~~~~~<1003>~~~~~~~~~~~~~~~~<1004>1.004113740e+005<1005>~~~~~~~~~~~~~~~~<1006>~~~~~~~~~~~~~~~~<1007>~~~~~~~~~~~~~~~~<1008>~~~~~~~~~~~~~~~~<1009>~~~~~~~~~~~~~~~~<1010>~~~~~~~~~~~~~~~~<1011>~~~~~~~~~~~~~~~~<1012>~~~~~~~~~~~~~~~~<1013>~~~~~~~~~~~~~~~~<1014>~~~~~~~~~~~~~~~~<1015>~~~~~~~~~~~~~~~~<1016>~~~~~~~~~~~~~~~~<1017>~~~~~~~~~~~~~~~~<1018>~~~~~~~~~~~~~~~~<1019>~~~~~~~~~~~~~~~~<1020>~~~~~~~~~~~~~~~~<1021>~~~~~~~~~~~~~~~~<1022>~~~~~~~~~~~~~~~~<1023>~~~~~~~~~~~~~~~~<1024>~~~~~~~~~~~~~~~~<1025>~~~~~~~~~~~~~~~~<1026>~~~~~~~~~~~~~~~~<1027>~~~~~~~~~~~~~~~~<1028>~~~~~~~~~~~~~~~~<1029>~~~~~~~~~~~~~~~~<1030>~~~~~~~~~~~~~~~~<1031>~~~~~~~~~~~~~~~~<1032>~~~~~~~~~~~~~~~~<1033>~~~~~~~~~~~~~~~~<1034>~~~~~~~~~~~~~~~~<1035>~~~~~~~~~~~~~~~~<1036>~~~~~~~~~~~~~~~~<1037>~~~~~~~~~~~~~~~~<1038>~~~~~~~~~~~~~~~~<1039>~~~~~~~~~~~~~~~~<1040>~~~~~~~~~~~~~~~~<1041>~~~~~~~~~~~~~~~~<1042>~~~~~~~~~~~~~~~~<1043>~~~~~~~~~~~~~~~~<1044>~~~~~~~~~~~~~~~~<1045>~~~~~~~~~~~~~~~~<1046>~~~~~~~~~~~~~~~~<1047>~~~~~~~~~~~~~~~~<1048>~~~~~~~~~~~~~~~~<1049>~~~~~~~~~~~~~~~~<1050>~~~~~~~~~~~~~~~~'
select id as id1,substring(@str1,id,1) as s1
into #tmp1
from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <= len(@str1)
order by id
select id as id2,substring(@str2,id,1) as s2
into #tmp2
from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id <= len(@str2)
order by id
declare @str varchar(8000)
set @str=''
select @str=@str+(case when s1>s2 or (s1 = '.' and s2 = '~') then s1 else s2 end) from (select * from #tmp1 full join #tmp2 on id1 = id2
) aa
set @str=right(@str,len(@str)-1)
select @str
drop table #tmp1,#tmp2
select getdate(),datediff(ms,@date1,getdate()) as 毫秒
/*
（所影响的行数为 1100 行）
（所影响的行数为 1100 行）
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1001>1.001150156e+005<1002>~~~~~~~~~~~~~~~~<1003>1.003440308e+005<1004>1~004113740e+005<1005>1.005473262e+005<1006>1.006921238e+005<1007>1.007874794e+004<1008>1.008783206e+005<1009>1.009795125e+005<1010>1.010351993e+005<1011>1.011101677e+005<1012>1.0122744
（所影响的行数为 1 行）
毫秒
------------------------------------------------------ -----------
2004-12-03 15:48:23.957 110
（所影响的行数为 1 行）
*/Top
36 楼txlicenhe（马可）回复于 2004-12-03 15:36:17 得分 13
我试了好多次都不到200毫秒。Top
37 楼vinsonshen（为了明天）回复于 2004-12-03 15:39:11 得分 2
高~~学习~~~Top
38 楼suhongshan（苏红山）回复于 2004-12-03 20:35:23 得分 2
好长的语句呀~！~Top
39 楼xym828（摄情）回复于 2004-12-03 21:25:14 得分 2
up,我看不懂哦,学习中Top
40 楼comefeel（刚中带柔）回复于 2004-12-03 21:31:42 得分 2
帮你顶
学习Top
41 楼guanshiyu123（就欺负你..宝）回复于 2004-12-03 21:53:26 得分 2
while(@num<10000)???为什么要用这个10000，
而不用len(@str1),len(@str2)中比较大的那个呢?Top
42 楼Static_X（）回复于 2004-12-04 14:45:03 得分 2
学习！Top
43 楼mnsiii（水流花谢两无情）回复于 2004-12-04 15:45:03 得分 2
我也学习！兼顶Top
44 楼jFresH_MaN（十一月的萧邦－夜曲）回复于 2004-12-04 16:15:46 得分 2
顶哦
学习一下Top
45 楼yugang1219（努力灌水）回复于 2004-12-04 16:40:51 得分 2
顶Top
46 楼huoyou342（飞雪零飘）回复于 2004-12-04 16:44:11 得分 2
顶Top
47 楼yyl001（70-229问题连载）回复于 2004-12-04 16:44:42 得分 2
学习，太长了，看不明白Top
48 楼aohan（aohan）回复于 2004-12-04 16:45:25 得分 2
我顶Top
49 楼ljz9425（阿九）回复于 2004-12-04 16:48:20 得分 2
GZTop
50 楼ccs02287（☆兜兜里有糖☆偶滴兜兜里有糖，你和我玩不？）回复于 2004-12-04 17:29:22 得分 2
不会!只有顶!希望能早解决!Top
51 楼Ncaidexiaoniao（）回复于 2004-12-04 20:20:37 得分 2
学习Top
52 楼peng1014（）回复于 2004-12-04 20:28:59 得分 2
学习Top
53 楼newsuperstar（与日月齐辉）回复于 2004-12-04 23:46:21 得分 2
顶Top
54 楼zgvslch（烟花离落）回复于 2004-12-05 00:34:33 得分 2
学习Top
55 楼didoleo（冷月无声）回复于 2004-12-05 17:07:54 得分 2
学习Top
56 楼FGhost33（^用实力证明自己的存在!^）回复于 2004-12-07 17:50:04 得分 2
upTop
57 楼librastar2001（天平）回复于 2004-12-08 13:50:02 得分 0
问题已经解决，感谢 txlicenhe(马可) ！！！以及各位帮本人顶贴的朋友！！！Top
58 楼librastar2001（天平）回复于 2004-12-08 13:56:42 得分 0
送分贴已经贴出！！！Top
59 楼librastar2001（天平）回复于 2004-12-08 13:57:56 得分 0
http://community.csdn.net/Expert/topic/3626/3626256.xml?temp=.6077082
http://community.csdn.net/Expert/topic/3626/3626262.xml?temp=.6100885
http://community.csdn.net/Expert/topic/3626/3626264.xml?temp=.7610895
http://community.csdn.net/Expert/topic/3626/3626267.xml?temp=.4231378
http://community.csdn.net/Expert/topic/3626/3626278.xml?temp=.7017023Top
60 楼masteryofsoft（天平）回复于 2004-12-08 14:11:59 得分 0
upTop
61 楼librastar2001（天平）回复于 2004-12-08 14:13:58 得分 0
了解FOR XML用法的人请进：
http://community.csdn.net/Expert/topic/3626/3626328.xml?temp=.3901483Top
相关问题
关键词
得分解答快速导航
- 帖主：librastar2001
- pweixing
- yyhyy23
- didoleo
- yelook
- YangYuWeb
- lionlzy
- qingbai
- pbsql
- tddw
- tddw
- huoyou342
- vinsonshen
- jiang130
- lmj2003
- lh1979
- freeheart1977
- yelook
- sxzqlzx
- txlicenhe
- vinsonshen
- suhongshan
- xym828
- comefeel
- guanshiyu123
- Static_X
- mnsiii
- jFresH_MaN
- yugang1219
- huoyou342
- yyl001
- aohan
- ljz9425
- ccs02287
- Ncaidexiaoniao
- peng1014
- newsuperstar
- zgvslch
- didoleo
- FGhost33
相关链接
广告也精彩
反馈
