create table A ( ID varchar(20), SM varchar(2000), ) create table B ( CZYID varchar(2000), QX text, )create table C ( QX varchar(20), SM varchar(2000), CZYID varchar(2000) )insert into A values('001','AAA') insert into A values('002','BBB') insert into A values('0022','CCC') insert into A values('003','DDD')insert into B values('1001','001, 002') insert into B values('1002','001, 003') insert into B values('1003','001, 002, 0022, 003') insert into B values('1004','003') declare @id varchar(20) declare @ffid varchar(20) declare @sm varchar(2000) declare @CZYID varchar(2000) declare @Resultstr varchar(2000) declare @QX varchar(2000) declare @countnum int DECLARE my_Cursor Cursor FOR SELECT id,sm FROM A OPEN my_Cursor FETCH NEXT FROM my_Cursor into @id,@sm WHILE @@FETCH_STATUS = 0 begin set @Resultstr=',' DECLARE myinner_Cursor Cursor FOR SELECT QX,CZYID FROM B OPEN myinner_Cursor FETCH NEXT FROM myinner_Cursor into @QX,@CZYID WHILE @@FETCH_STATUS = 0 begin set @countnum=0 set @ffid=ltrim(rtrim(@id)) select @countnum=count(*) from split(@QX,',') where ltrim(rtrim(F1))=@ffid if(@countnum>0) begin set @Resultstr=@Resultstr+@CZYID+', ' end FETCH NEXT FROM myinner_Cursor into @QX,@CZYID end CLOSE myinner_Cursor DEALLOCATE myinner_Cursor insert into C values(@id,@sm,@Resultstr) FETCH NEXT FROM my_Cursor into @id,@sm end CLOSE my_Cursor DEALLOCATE my_Cursor select * from C
忘记了 个函数 create function [dbo].[split] ( @SourceSql varchar(8000), @StrSeprate varchar(10) ) returns @temp table(F1 varchar(100)) as begin declare @i int set @SourceSql = rtrim(ltrim(@SourceSql)) set @i = charindex(@StrSeprate,@SourceSql) while @i >= 1 begin if len(left(@SourceSql,@i-1))>0 begin insert @temp values(left(@SourceSql,@i-1)) end set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql <> '' insert @temp values(@SourceSql) return end create table A ( ID varchar(20), SM varchar(2000), ) create table B ( CZYID varchar(2000), QX text, )create table C ( QX varchar(20), SM varchar(2000), CZYID varchar(2000) )insert into A values('001','AAA') insert into A values('002','BBB') insert into A values('0022','CCC') insert into A values('003','DDD')insert into B values('1001','001, 002') insert into B values('1002','001, 003') insert into B values('1003','001, 002, 0022, 003') insert into B values('1004','003') declare @id varchar(20) declare @ffid varchar(20) declare @sm varchar(2000) declare @CZYID varchar(2000) declare @Resultstr varchar(2000) declare @QX varchar(2000) declare @countnum int DECLARE my_Cursor Cursor FOR SELECT id,sm FROM A OPEN my_Cursor FETCH NEXT FROM my_Cursor into @id,@sm WHILE @@FETCH_STATUS = 0 begin set @Resultstr=',' DECLARE myinner_Cursor Cursor FOR SELECT QX,CZYID FROM B OPEN myinner_Cursor FETCH NEXT FROM myinner_Cursor into @QX,@CZYID WHILE @@FETCH_STATUS = 0 begin set @countnum=0 set @ffid=ltrim(rtrim(@id)) select @countnum=count(*) from split(@QX,',') where ltrim(rtrim(F1))=@ffid if(@countnum>0) begin set @Resultstr=@Resultstr+@CZYID+', ' end FETCH NEXT FROM myinner_Cursor into @QX,@CZYID end CLOSE myinner_Cursor DEALLOCATE myinner_Cursor insert into C values(@id,@sm,@Resultstr) FETCH NEXT FROM my_Cursor into @id,@sm end CLOSE my_Cursor DEALLOCATE my_Cursor select * from C
提示在关键字 'declare' 附近有语法错误。
提示在关键字 'declare' 附近有语法错误。
If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'TF' AND B.NAME = 'split' )DROP function split GO create function [dbo].[split] ( @SourceSql varchar(8000), @StrSeprate varchar(10) ) returns @temp table(F1 varchar(100)) as begin declare @i int set @SourceSql = rtrim(ltrim(@SourceSql)) set @i = charindex(@StrSeprate,@SourceSql) while @i >= 1 begin if len(left(@SourceSql,@i-1))>0 begin insert @temp values(left(@SourceSql,@i-1)) end set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql <> '' insert @temp values(@SourceSql) return end go If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'u' AND B.NAME = 'A' )DROP table A GO create table A ( ID varchar(20), SM varchar(2000), ) go If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'u' AND B.NAME = 'B' )DROP table B GO create table B ( CZYID varchar(2000), QX text, ) go If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'u' AND B.NAME = 'c' )DROP table c GO create table C ( QX varchar(20), SM varchar(2000), CZYID varchar(2000) )insert into A values('001','AAA') insert into A values('002','BBB') insert into A values('0022','CCC') insert into A values('003','DDD')insert into B values('1001','001, 002') insert into B values('1002','001, 003') insert into B values('1003','001, 002, 0022, 003') insert into B values('1004','003') declare @id varchar(20) declare @ffid varchar(20) declare @sm varchar(2000) declare @CZYID varchar(2000) declare @Resultstr varchar(2000) declare @QX varchar(2000) declare @countnum int DECLARE my_Cursor Cursor FOR SELECT id,sm FROM A OPEN my_Cursor FETCH NEXT FROM my_Cursor into @id,@sm WHILE @@FETCH_STATUS = 0 begin set @Resultstr=',' DECLARE myinner_Cursor Cursor FOR SELECT QX,CZYID FROM B OPEN myinner_Cursor FETCH NEXT FROM myinner_Cursor into @QX,@CZYID WHILE @@FETCH_STATUS = 0 begin set @countnum=0 set @ffid=ltrim(rtrim(@id)) select @countnum=count(*) from split(@QX,',') where ltrim(rtrim(F1))=@ffid if(@countnum>0) begin set @Resultstr=@Resultstr+@CZYID+', ' end FETCH NEXT FROM myinner_Cursor into @QX,@CZYID end CLOSE myinner_Cursor DEALLOCATE myinner_Cursor insert into C values(@id,@sm,@Resultstr) FETCH NEXT FROM my_Cursor into @id,@sm end CLOSE my_Cursor DEALLOCATE my_Cursor select * from C
--> 测试数据: #ta if object_id('tempdb.dbo.#ta') is not null drop table #ta go create table #ta (ID varchar(4),SM varchar(3)) insert into #ta select '001','AAA' union all select '002','BBB' union all select '0022','CCC' union all select '003','DDD' --> 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (CZYID varchar(14),QX text ) insert into #tb select '1001','001,002' union all select '1002','001,003' union all select '1003','001,002,0022,003' union all select '1004','003' ;with cte as ( select * from #ta ,#tb where charindex(','+id+',',','+cast(qx as varchar(max))+',')>0 ) select ID,SM, CZYID=stuff((select ','+CZYID from cte where id=t.id for xml path('')),1,1,'') from cte t group by id,sm ID SM CZYID ---- ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 001 AAA 1001,1002,1003 002 BBB 1001,1003 0022 CCC 1003 003 DDD 1002,1003,1004(4 行受影响)
--Create table create table A ( ID varchar(20), SM varchar(2000), ) create table B ( CZYID varchar(2000), QX varchar(2000), )create table C ( ID varchar(20), CZYID varchar(2000) )--Insert data insert into A values('001','AAA') insert into A values('002','BBB') insert into A values('0022','CCC') insert into A values('003','DDD')insert into B values('1001','001,002') insert into B values('1002','001,003') insert into B values('1003','001,002,0022,003') insert into B values('1004','003')--create temp table SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b --Insert data to the third table insert into C SELECT B.CZYID, SUBSTRING(B.[QX], #.id, CHARINDEX(',', B.[QX] + ',', #.id) - #.id) AS id FROM B, # WHERE SUBSTRING(',' + B.[QX], #.id, 1) = ','--create function create function dbo.f_str(@id varchar(20)) returns varchar(100) as begin declare @str varchar(1000) set @str = '' select @str = @str + ',' + cast(ID as varchar) from C where CZYID = @id set @str = right(@str , len(@str) - 1) return @str end go --Used the funtion select ID,SM, dbo.f_str(id) as CZYID from A group by id,SM/* ID SM CZYID 001 AAA 1001,1002,1003 002 BBB 1001,1003 0022 CCC 1003 003 DDD 1002,1003,1004 */ 多用了个表,可以把函数里面的 表C 换成 表变量 ,如:declare @t table(uniondate int) insert into @t select top 100 isnull((select count(1) from sysobjects where id<t.id),0) from sysobjects t select * from @t
create table tba(ID varchar(4),SM varchar(3)) insert into tba select '001','AAA' union all select '002','BBB' union all select '0022','CCC' union all select '003','DDD' create table tbb(CZYID varchar(14),QX varchar(30)) insert into tbb select '1001','001,002' union all select '1002','001,003' union all select '1003','001,002,0022,003' union all select '1004','003' go ;with cte as( select a.id,a.sm,b.czyid from tba a inner join tbb b on charindex(','+a.id+',',','+b.qx+',')>0 ) select distinct * from( select id,sm,stuff((select ','+czyid from cte where id=t.id for xml path('')),1,1,'') as czyid from cte t )d go drop table tba,tbb
(
ID varchar(20),
SM varchar(2000),
)
create table B
(
CZYID varchar(2000),
QX text,
)create table C
(
QX varchar(20),
SM varchar(2000),
CZYID varchar(2000)
)insert into A values('001','AAA')
insert into A values('002','BBB')
insert into A values('0022','CCC')
insert into A values('003','DDD')insert into B values('1001','001, 002')
insert into B values('1002','001, 003')
insert into B values('1003','001, 002, 0022, 003')
insert into B values('1004','003')
declare @id varchar(20)
declare @ffid varchar(20)
declare @sm varchar(2000)
declare @CZYID varchar(2000)
declare @Resultstr varchar(2000)
declare @QX varchar(2000)
declare @countnum int
DECLARE my_Cursor Cursor FOR
SELECT id,sm
FROM A
OPEN my_Cursor
FETCH NEXT FROM my_Cursor
into @id,@sm
WHILE @@FETCH_STATUS = 0
begin
set @Resultstr=','
DECLARE myinner_Cursor Cursor FOR
SELECT QX,CZYID
FROM B
OPEN myinner_Cursor
FETCH NEXT FROM myinner_Cursor
into @QX,@CZYID
WHILE @@FETCH_STATUS = 0
begin
set @countnum=0
set @ffid=ltrim(rtrim(@id))
select @countnum=count(*) from split(@QX,',') where ltrim(rtrim(F1))=@ffid
if(@countnum>0)
begin
set @Resultstr=@Resultstr+@CZYID+', '
end
FETCH NEXT FROM myinner_Cursor into @QX,@CZYID
end
CLOSE myinner_Cursor
DEALLOCATE myinner_Cursor
insert into C values(@id,@sm,@Resultstr)
FETCH NEXT FROM my_Cursor into @id,@sm
end
CLOSE my_Cursor
DEALLOCATE my_Cursor select * from C
忘记了 个函数
create function [dbo].[split]
( @SourceSql varchar(8000), @StrSeprate varchar(10) )
returns @temp table(F1 varchar(100))
as begin
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @i = charindex(@StrSeprate,@SourceSql)
while @i >= 1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql <> ''
insert @temp values(@SourceSql)
return
end create table A
(
ID varchar(20),
SM varchar(2000),
)
create table B
(
CZYID varchar(2000),
QX text,
)create table C
(
QX varchar(20),
SM varchar(2000),
CZYID varchar(2000)
)insert into A values('001','AAA')
insert into A values('002','BBB')
insert into A values('0022','CCC')
insert into A values('003','DDD')insert into B values('1001','001, 002')
insert into B values('1002','001, 003')
insert into B values('1003','001, 002, 0022, 003')
insert into B values('1004','003')
declare @id varchar(20)
declare @ffid varchar(20)
declare @sm varchar(2000)
declare @CZYID varchar(2000)
declare @Resultstr varchar(2000)
declare @QX varchar(2000)
declare @countnum int
DECLARE my_Cursor Cursor FOR
SELECT id,sm
FROM A
OPEN my_Cursor
FETCH NEXT FROM my_Cursor
into @id,@sm
WHILE @@FETCH_STATUS = 0
begin
set @Resultstr=','
DECLARE myinner_Cursor Cursor FOR
SELECT QX,CZYID
FROM B
OPEN myinner_Cursor
FETCH NEXT FROM myinner_Cursor
into @QX,@CZYID
WHILE @@FETCH_STATUS = 0
begin
set @countnum=0
set @ffid=ltrim(rtrim(@id))
select @countnum=count(*) from split(@QX,',') where ltrim(rtrim(F1))=@ffid
if(@countnum>0)
begin
set @Resultstr=@Resultstr+@CZYID+', '
end
FETCH NEXT FROM myinner_Cursor into @QX,@CZYID
end
CLOSE myinner_Cursor
DEALLOCATE myinner_Cursor
insert into C values(@id,@sm,@Resultstr)
FETCH NEXT FROM my_Cursor into @id,@sm
end
CLOSE my_Cursor
DEALLOCATE my_Cursor select * from C
GO
create function [dbo].[split]
( @SourceSql varchar(8000), @StrSeprate varchar(10) )
returns @temp table(F1 varchar(100))
as begin
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @i = charindex(@StrSeprate,@SourceSql)
while @i >= 1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql <> ''
insert @temp values(@SourceSql)
return
end go
If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'u' AND B.NAME = 'A' )DROP table A
GO
create table A
(
ID varchar(20),
SM varchar(2000),
)
go
If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'u' AND B.NAME = 'B' )DROP table B
GO
create table B
(
CZYID varchar(2000),
QX text,
)
go
If exists(SELECT * FROM DBO.SYSOBJECTS B WHERE B.XTYPE = 'u' AND B.NAME = 'c' )DROP table c
GO
create table C
(
QX varchar(20),
SM varchar(2000),
CZYID varchar(2000)
)insert into A values('001','AAA')
insert into A values('002','BBB')
insert into A values('0022','CCC')
insert into A values('003','DDD')insert into B values('1001','001, 002')
insert into B values('1002','001, 003')
insert into B values('1003','001, 002, 0022, 003')
insert into B values('1004','003')
declare @id varchar(20)
declare @ffid varchar(20)
declare @sm varchar(2000)
declare @CZYID varchar(2000)
declare @Resultstr varchar(2000)
declare @QX varchar(2000)
declare @countnum int
DECLARE my_Cursor Cursor FOR
SELECT id,sm
FROM A
OPEN my_Cursor
FETCH NEXT FROM my_Cursor
into @id,@sm
WHILE @@FETCH_STATUS = 0
begin
set @Resultstr=','
DECLARE myinner_Cursor Cursor FOR
SELECT QX,CZYID
FROM B
OPEN myinner_Cursor
FETCH NEXT FROM myinner_Cursor
into @QX,@CZYID
WHILE @@FETCH_STATUS = 0
begin
set @countnum=0
set @ffid=ltrim(rtrim(@id))
select @countnum=count(*) from split(@QX,',') where ltrim(rtrim(F1))=@ffid
if(@countnum>0)
begin
set @Resultstr=@Resultstr+@CZYID+', '
end
FETCH NEXT FROM myinner_Cursor into @QX,@CZYID
end
CLOSE myinner_Cursor
DEALLOCATE myinner_Cursor
insert into C values(@id,@sm,@Resultstr)
FETCH NEXT FROM my_Cursor into @id,@sm
end
CLOSE my_Cursor
DEALLOCATE my_Cursor select * from C
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (ID varchar(4),SM varchar(3))
insert into #ta
select '001','AAA' union all
select '002','BBB' union all
select '0022','CCC' union all
select '003','DDD'
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (CZYID varchar(14),QX text )
insert into #tb
select '1001','001,002' union all
select '1002','001,003' union all
select '1003','001,002,0022,003' union all
select '1004','003' ;with cte as
(
select * from #ta ,#tb where charindex(','+id+',',','+cast(qx as varchar(max))+',')>0
)
select ID,SM,
CZYID=stuff((select ','+CZYID from cte where id=t.id for xml path('')),1,1,'')
from cte t
group by id,sm
ID SM CZYID
---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001 AAA 1001,1002,1003
002 BBB 1001,1003
0022 CCC 1003
003 DDD 1002,1003,1004(4 行受影响)
create table A
(
ID varchar(20),
SM varchar(2000),
)
create table B
(
CZYID varchar(2000),
QX varchar(2000),
)create table C
(
ID varchar(20),
CZYID varchar(2000)
)--Insert data
insert into A values('001','AAA')
insert into A values('002','BBB')
insert into A values('0022','CCC')
insert into A values('003','DDD')insert into B values('1001','001,002')
insert into B values('1002','001,003')
insert into B values('1003','001,002,0022,003')
insert into B values('1004','003')--create temp table
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b --Insert data to the third table
insert into C
SELECT B.CZYID, SUBSTRING(B.[QX], #.id, CHARINDEX(',', B.[QX] + ',', #.id) - #.id) AS id
FROM B, #
WHERE SUBSTRING(',' + B.[QX], #.id, 1) = ','--create function
create function dbo.f_str(@id varchar(20)) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(ID as varchar) from C where CZYID = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go
--Used the funtion
select ID,SM, dbo.f_str(id) as CZYID from A group by id,SM/*
ID SM CZYID
001 AAA 1001,1002,1003
002 BBB 1001,1003
0022 CCC 1003
003 DDD 1002,1003,1004
*/
多用了个表,可以把函数里面的 表C 换成 表变量 ,如:declare @t table(uniondate int)
insert into @t select top 100 isnull((select count(1) from sysobjects where id<t.id),0) from sysobjects t
select * from @t
insert into tba
select '001','AAA' union all
select '002','BBB' union all
select '0022','CCC' union all
select '003','DDD'
create table tbb(CZYID varchar(14),QX varchar(30))
insert into tbb
select '1001','001,002' union all
select '1002','001,003' union all
select '1003','001,002,0022,003' union all
select '1004','003'
go
;with cte as(
select a.id,a.sm,b.czyid from tba a inner join tbb b on charindex(','+a.id+',',','+b.qx+',')>0
)
select distinct * from(
select id,sm,stuff((select ','+czyid from cte where id=t.id for xml path('')),1,1,'') as czyid from cte t
)d
go
drop table tba,tbb