--try... declare @t table ( name varchar(50) ) ;with cte(t) as( select substring(name,0,charindex(',',name)) from a union all select substring(name,charindex(',',name)+1,len(name)) from a ) insert into @t select * from cte where t<>''update ... from a,@t where ...go
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A GOCREATE TABLE A ( ID INT, NAME VARCHAR(50) ) GO INSERT INTO A SELECT 1,'张三,李四' UNION ALL SELECT 2,'王五' UNION ALL SELECT 3,'马六,朱七' GO ;with cte(t) as( select substring(name,0,charindex(',',name)) from a union all select substring(name,charindex(',',name)+1,len(name)) from a ) insert into A select * from cte where t<>''
6楼的代码最后insert那里少了列名;with cte(t) as( select substring(name,0,charindex(',',name)) from a union all select substring(name,charindex(',',name)+1,len(name)) from a ) insert into A([name]) select * from cte where t<>'' 这就正确了
请问有什么好的方法没?
能不能用update语句实现 ·?
declare @t table
(
name varchar(50)
)
;with cte(t) as(
select substring(name,0,charindex(',',name)) from a
union all
select substring(name,charindex(',',name)+1,len(name)) from a
)
insert into @t
select * from cte where t<>''update ... from a,@t where ...go
DROP TABLE A
GOCREATE TABLE A
(
ID INT,
NAME VARCHAR(50)
)
GO
INSERT INTO A
SELECT 1,'张三,李四'
UNION ALL
SELECT 2,'王五'
UNION ALL
SELECT 3,'马六,朱七'
GO
;with cte(t) as(
select substring(name,0,charindex(',',name)) from a
union all
select substring(name,charindex(',',name)+1,len(name)) from a
)
insert into A
select * from cte where t<>''
select substring(name,0,charindex(',',name)) from a
union all
select substring(name,charindex(',',name)+1,len(name)) from a
)
insert into A([name])
select * from cte where t<>''
这就正确了