select (len(name)-len(replace(name,''北京'')))/len('北京') name from table7
union
select (len(home)-len(replace(home,'南京','')))/len('南京') home from
table7 into (你要的表名)
union
select (len(home)-len(replace(home,'南京','')))/len('南京') home from
table7 into (你要的表名)
union
select (len(home)-len(replace(home,'南京','')))/len('南京') home from
table7 into (你要的表名)
应该就可以啦,你试一试啊!
from
(select ID,(len(name)-len(replace(name,''北京'')))/len('北京') name
from table7 )a
inner join (
select ID,(len(home)-len(replace(home,'南京','')))/len('南京') home
from table7)b on a.ID=b.ID
from(
select (len(name)-len(replace(name,''北京'')))/len('北京') name from table7
union all
select (len(home)-len(replace(home,'南京','')))/len('南京') home from table7
) a
insert into 表
select * from(
select (len(name)-len(replace(name,''北京'')))/len('北京') name from table7
union all
select (len(home)-len(replace(home,'南京','')))/len('南京') home from table7
) a
into #tb1 from table7select id=identity(int,1,1),(len(home)-len(replace(home,'南京','')))/len('南京') home
into #tb2 from table7 --合并结果
select a.name,b.home into 要生成的表
from #tb1 a full join #tb2 b on a.id=b.id或:
insert into 表
select a.name,b.home from #tb1 a full join #tb2 b on a.id=b.id--删除临时表
drop table #tb1,#tb2
然后这样写代码:
INSERT aa (name,home)
SELECT (len(name)-len(replace(name,''北京'')))/len('北京'),
(len(home)-len(replace(home,'南京','')))/len('南京')
FROM table7