--try
if object_id('tb') is not null
drop table tb
if object_id('pro_c')is not null
drop procedure pro_c
go
create table tb (id1 int,id2 int)
insert into tb select 1,2
union all select 1,3
union all select 2,4
union all select 5,4
go
create procedure pro_c
@id nvarchar(10)
as
begin
declare @str nvarchar(10)
set @str=''
select @str=@str+' '+ ltrim(id2) from tb where charindex(ltrim(id1),@id)>0
select @str
end
go
exec pro_c 125
/*
2 3 4 4
(1 行受影响
*/
--1)表和数据
CREATE TABLE #for_csdn
(id1 int,
id2 int)
INSERT INTO #for_csdn VALUES(1,2)
INSERT INTO #for_csdn VALUES(1,3)
INSERT INTO #for_csdn VALUES(2,4)
INSERT INTO #for_csdn VALUES(5,4)
--2)代码段
DECLARE @str varchar(MAX)
SET @str = ''
SELECT @str = ISNULL(@str + ' ','')+CAST(IDEN AS varchar(30))FROM
(
SELECT IDEN = id2 FROM #for_csdn B
INNER JOIN
(
SELECT id1 FROM #for_csdn
GROUP BY id1
HAVING COUNT(1) > 1)A
ON A.id1 = B.id1
UNION
SELECT IDEN = id1 FROM #for_csdn C
INNER JOIN (
SELECT id2 FROM #for_csdn
GROUP BY id2
HAVING COUNT(1) > 1)D
ON C.id2 = D.id2
UNION
SELECT IDEN = id2 FROM #for_csdn
GROUP BY id2
HAVING COUNT(1) > 1)F
SELECT iden = @str
--3)结果
iden
--------------------------------------
2 3 4 5
(1 row(s) affected)
declare @t table (id int,id2 int)
insert @t
select 1,2 union all
select 1,3 union all
select 2,4 union all
select 5,4
select * from @t
declare @var varchar(20)
select @var=isnull(@var+',','')+ cast(id2 as varchar(20)) from @t
select c= @var
