程序介绍：两张表a,b合成一张表ae.
a表两列（OrderID（编号），jgz（甲骨字））6155行。
b表两列(OrderID（编号），yw（一段甲骨字）)45515行，yw（一段甲骨字）里的有a表中的甲骨字构成。
ae表三列(记录编号（一段甲骨字的编号),编号（甲骨字编号），次数 （一段甲骨字中每个甲骨字出现的次数））
目的：提高算法执行效率。45515条记录，我执行的半个多小时。谢谢！！！
create table ae
( 记录编号 int,
编号 int not null,
次数 int
)
declare @i1 int,@c1 nvarchar(MAX), @i int,@c nvarchar(1),@c3 int
set @i1=1
while @i1<45515
begin
set @c1=
(select yw from b where [email protected])
select @i=1
while @i <=len(@c1)
begin
select @c=substring(@c1,@i,1),@[email protected]+1
set @c3=
(select OrderID from a where [email protected])
update ac set 次数=次数+1 where [email protected] and [email protected]
if @@rowcount=0
insert into ac (记录编号,编号,次数) values(@i1,@c3,1)
end
set @[email protected]+1
end
------解决方案--------------------
- SQL code
select b.orderID as 记录编号,a.orderID as 编号, len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数from a,bwhere charindex(a.jgz,b.yw) > 0
------解决方案--------------------
- SQL code
select b.orderid as 记录编号,a.orderid as 编号,len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数 from a,b where exists(select 1 from b where charindex(a.jgz,b.yw) > 0)
------解决方案--------------------
- SQL code
select b.orderid as 记录编号,a.orderid as 编号,len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数 from a where exists(select 1 from b where charindex(a.jgz,b.yw) > 0)
------解决方案--------------------
- SQL code
select b.orderID as 记录编号,a.orderID as 编号,len(b.yw)-len(replace(b.yw,a.jgz,'')) as 次数from a,bwhere b.yw like '%'+a.jgz+'%'