一个表: ID number 001 1,2 002 1,2,3 别一个表 ID name 1 aaaa 2 bbbbbbb 3 cccccc 结果: ID num_Name 001 aaaa,bbbbbbb 002 aaaa,bbbbbbb,cccccc
--> liangCK小梁 于2008-10-14 --> 生成测试数据: #tb1 IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1 CREATE TABLE #tb1 (ID VARCHAR(3),number VARCHAR(5)) INSERT INTO #tb1 SELECT '001','1,2' UNION ALL SELECT '002','1,2,3' --> liangCK小梁 于2008-10-14 --> 生成测试数据: #tb2 IF OBJECT_ID('tempdb.dbo.tb2') IS NOT NULL DROP TABLE tb2 CREATE TABLE tb2 (ID INT,name VARCHAR(7)) INSERT INTO tb2 SELECT '1','aaaa' UNION ALL SELECT '2','bbbbbbb' UNION ALL SELECT '3','cccccc'--SQL查询如下: GO CREATE FUNCTION dbo.ReplaceNumber(@number VARCHAR(5)) RETURNS VARCHAR(30) AS BEGIN DECLARE @re VARCHAR(30); SET @re='';
SELECT @re=@re+','+REPLACE(ID,RTRIM(ID),name) FROM tb2 WHERE CHARINDEX(','+RTRIM(ID)+',',','+@number+',')>0;
RETURN STUFF(@re,1,1,''); END GOSELECT ID,dbo.ReplaceNumber(number) number FROM #tb1 DROP FUNCTION dbo.ReplaceNumber DROP TABLE tb2/* ID number ---- ------------------------------ 001 aaaa,bbbbbbb 002 aaaa,bbbbbbb,cccccc(2 行受影响)*/
--2000使用函数或临时表,05用 xml declare @ta table(id varchar(50),number varchar(50)) insert into @ta select '001','1,2' insert into @ta select '002','1,2,3' declare @tb table(id varchar(50),name varchar(50)) insert into @tb select 1,'aaaa' insert into @tb select 2,'bbbbbbb' insert into @tb select 3,'cccccc'select a.id,number=stuff( (select ','+name from @tb where charindex(','+ltrim(id)+',',','+a.number+',')>0 for xml path(''),root('r'),type).value('/r[1]','nvarchar(max)'),1,1,'') from @ta aid number 001 aaaa,bbbbbbb 002 aaaa,bbbbbbb,cccccc
------------------------------------ -- Author: happyflsytone -- Date:2008-10-14 16:17:36 -------------------------------------- Test Data: ta IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta(ID NVARCHAR(3),number varchar(20)) Go INSERT INTO ta SELECT '001','1,2' UNION ALL SELECT '002','1,2,3' GO -- Test Data: tb IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb Go CREATE TABLE tb(ID INT,name NVARCHAR(7)) Go INSERT INTO tb SELECT 1,'aaaa' UNION ALL SELECT 2,'bbbbbbb' UNION ALL SELECT 3,'cccccc' GO --Start select c.id,number = stuff(replace(replace(( select b.name as name from ta a left join ( select id,name from tb ) b on charindex(','+ltrim(b.id)+',',','+a.number+',')>0 where a.id = c.id for xml auto ),'"/><b name="',','),'"/>',''),1,9,'') from ta c --Result: /*id number ---- ------------------- 001 aaaa,bbbbbbb 002 aaaa,bbbbbbb,cccccc(2 行受影响) */ --End
一个表:
ID number
001 1,2
002 1,2,3 别一个表 ID name
1 aaaa
2 bbbbbbb
3 cccccc 结果:
ID num_Name
001 aaaa,bbbbbbb
002 aaaa,bbbbbbb,cccccc
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (ID VARCHAR(3),number VARCHAR(5))
INSERT INTO #tb1
SELECT '001','1,2' UNION ALL
SELECT '002','1,2,3'
--> liangCK小梁 于2008-10-14
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.tb2') IS NOT NULL DROP TABLE tb2
CREATE TABLE tb2 (ID INT,name VARCHAR(7))
INSERT INTO tb2
SELECT '1','aaaa' UNION ALL
SELECT '2','bbbbbbb' UNION ALL
SELECT '3','cccccc'--SQL查询如下:
GO
CREATE FUNCTION dbo.ReplaceNumber(@number VARCHAR(5))
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @re VARCHAR(30);
SET @re='';
SELECT @re=@re+','+REPLACE(ID,RTRIM(ID),name)
FROM tb2
WHERE CHARINDEX(','+RTRIM(ID)+',',','+@number+',')>0;
RETURN STUFF(@re,1,1,'');
END
GOSELECT ID,dbo.ReplaceNumber(number) number
FROM #tb1 DROP FUNCTION dbo.ReplaceNumber
DROP TABLE tb2/*
ID number
---- ------------------------------
001 aaaa,bbbbbbb
002 aaaa,bbbbbbb,cccccc(2 行受影响)*/
--2000使用函数或临时表,05用 xml
declare @ta table(id varchar(50),number varchar(50))
insert into @ta select '001','1,2'
insert into @ta select '002','1,2,3'
declare @tb table(id varchar(50),name varchar(50))
insert into @tb select 1,'aaaa'
insert into @tb select 2,'bbbbbbb'
insert into @tb select 3,'cccccc'select a.id,number=stuff(
(select ','+name from @tb where charindex(','+ltrim(id)+',',','+a.number+',')>0 for xml path(''),root('r'),type).value('/r[1]','nvarchar(max)'),1,1,'')
from @ta aid number
001 aaaa,bbbbbbb
002 aaaa,bbbbbbb,cccccc
-- Author: happyflsytone
-- Date:2008-10-14 16:17:36
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(ID NVARCHAR(3),number varchar(20))
Go
INSERT INTO ta
SELECT '001','1,2' UNION ALL
SELECT '002','1,2,3'
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(ID INT,name NVARCHAR(7))
Go
INSERT INTO tb
SELECT 1,'aaaa' UNION ALL
SELECT 2,'bbbbbbb' UNION ALL
SELECT 3,'cccccc'
GO
--Start
select c.id,number = stuff(replace(replace((
select b.name as name
from ta a
left join
( select id,name
from tb
) b
on charindex(','+ltrim(b.id)+',',','+a.number+',')>0
where a.id = c.id
for xml auto
),'"/><b name="',','),'"/>',''),1,9,'')
from ta c
--Result:
/*id number
---- -------------------
001 aaaa,bbbbbbb
002 aaaa,bbbbbbb,cccccc(2 行受影响)
*/
--End