查询数据插入问题 多条

[pdgzzgx]

insert into news_hq (ts_cs,ts_pm) select ts_cs,ts_pm from news


如果ts_pm ='aa-bb-cc'就插入3条 ts_cs 相同
相当于有"-"就多插入一条记录

[rea1gz]

要用函数

[pdgzzgx]

函数怎么用？

[rea1gz]

不用函数了，直接用表变量


declare @t table(
ts_cs varchar(20), --改你的数据类型
ts_pm varchar(2000)
)
insert @t select ts_cs,ts_pm from news

declare @r table(
ts_cs varchar(20), --改你的数据类型
ts_pm varchar(20)
)

while exists (select * from @t)
begin
--插入前半部分
insert into news_hq (ts_cs,ts_pm) select ts_cs,left(ts_pm,charindex('-',ts_pm)-1) from @t where charindex('-',ts_pm)>0

update @t set ts_pm=stuff(ts_pm,1,charindex('-',ts_pm),'')
where charindex('-',ts_pm)>0

delete @t where ts_pm is null or ts_pm=''

insert into news_hq (ts_cs,ts_pm) select ts_cs,ts_pm from @t where charindex('-',ts_pm)<=0

delete @t where charindex('-',ts_pm)<=0

end

[rea1gz]

改下：

declare @t table(
ts_cs varchar(20), --改你的数据类型
ts_pm varchar(2000)
)
insert @t select ts_cs,ts_pm from news

while exists (select * from @t)
begin
--插入前半部分
insert into news_hq (ts_cs,ts_pm) select ts_cs,left(ts_pm,charindex('-',ts_pm)-1) from @t where charindex('-',ts_pm)>0

update @t set ts_pm=stuff(ts_pm,1,charindex('-',ts_pm),'')
where charindex('-',ts_pm)>0

delete @t where ts_pm is null or ts_pm=''

insert into news_hq (ts_cs,ts_pm) select ts_cs,ts_pm from @t where charindex('-',ts_pm)<=0

delete @t where charindex('-',ts_pm)<=0

end

[pdgzzgx]

rea1gz(冒牌realgz V0.3) 刚才还在分析你给我写的函数。
你又给我回答
相信你。谢谢你

[hellowork]

这样试试:
declare @news table(ts_cs varchar(100),ts_pm varchar(100))
declare @news_hq table(ts_cs varchar(100),ts_pm varchar(100))
insert @news
select 'a','aa-bb-cc' union all
select 'b','aa-bb-cc-dd' union all
select 'c','aa-bb-cc-ff-gg'

select top 100 id = identity(int,1,1) into #tmp from syscolumns
insert @news_hq
select a.* from @news a inner join #tmp b on substring('-' + a.ts_pm,b.id,1) = '-'
----查看
select * from @news_hq

drop table #tmp

[pdgzzgx]

rea1gz(冒牌realgz V0.3)
能给我写成函数是最好的。
因为我这个sql还有很多其他的数据要处理，我这里只写了2个字段。其实很多。
而且我都是写成函数的。
麻烦你一下了

[pdgzzgx]

hellowork(一两清风) ( ) 信誉：100 Blog

你的分出来是可以，但是
结果
aa-bb-cc
aa-bb-cc
aa-bb-cc
.
.
.
.
我要的是"
aa
bb
cc

[hellowork]

那就这样:
declare @news table(ts_cs varchar(100),ts_pm varchar(100))
declare @news_hq table(ts_cs varchar(100),ts_pm varchar(100))
insert @news
select 'a','aa-bb-cc' union all
select 'b','aa-bb-cc-dd' union all
select 'c','aa-bb-cc-ff-gg'

select top 100 id = identity(int,1,1) into #tmp from syscolumns
insert @news_hq(ts_cs,ts_pm)
select a.ts_cs,substring(a.ts_pm,b.id,1) as ts_pm
from @news a inner join #tmp b on substring('-' + a.ts_pm,b.id,1) = '-'
----查看
select * from @news_hq

drop table #tmp

[pdgzzgx]

改为我的程序 错误:

向substring 函数传递无效的length参数

[rea1gz]

晕，今天我没写函数

[hellowork]

请楼主把
select top 100 id = identity(int,1,1) into #tmp from syscolumns
改为:
select top 5000 id = identity(int,1,1) into #tmp from master..syscolumns