A表 ID FValue B表 FValue
1 01 01;02;03;04
1 02 02;04;05
1 03 01;03;05;06
2 04
2 05
2 06B表如何得到如下结果: F1 F2 (根据A表ID的值将B表的字段分开)
01;02;03 04
02 04;05
01;03 05;06
1 01 01;02;03;04
1 02 02;04;05
1 03 01;03;05;06
2 04
2 05
2 06B表如何得到如下结果: F1 F2 (根据A表ID的值将B表的字段分开)
01;02;03 04
02 04;05
01;03 05;06
insert A
select 1 ,'01'
union select 1 ,'02'
union select 1 ,'03'
union select 2 ,'04'
union select 2 ,'05'
union select 2 ,'06'
create table B (FValue varchar(100))
insert B
select '01;02;03;04'
union select '02;04;05'
union select '01;03;05;06'
go
create function f(@id varchar(1000),@flag int)
returns varchar(1000)
as
begin
declare @str varchar(1000) select @str=isnull(@str,'')+f1+f2+';' from (select b.fvalue,id,f1=case when id = 1 then a.fvalue else '' end,f2=case when id = 2 then a.fvalue else '' end from B inner join A on charindex(a.FValue,b.FValue)>0 ) t where fvalue = @id and id = @flag
return @strend
goselect dbo.f(fvalue,1),dbo.f(fvalue,2) from b
drop table A,B
drop function f
来个SQL2000的
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-03 11:56:11
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID INT,FValue VARCHAR(2))
INSERT INTO @tb1
SELECT 1,'01' UNION ALL
SELECT 1,'02' UNION ALL
SELECT 1,'03' UNION ALL
SELECT 2,'04' UNION ALL
SELECT 2,'05' UNION ALL
SELECT 2,'06' UNION ALL
SELECT 3,'07' UNION ALL
SELECT 3,'08'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (FValue VARCHAR(11))
INSERT INTO @tb2
SELECT '01;02;03;04' UNION ALL
SELECT '02;04;05' UNION ALL
SELECT '01;03;05;06' UNION ALL
SELECT '01;07'--SQL查询如下:;WITH LiangLoveLan AS
(
SELECT A.rowid,A.FValue,B.ID
FROM
(
SELECT
rowid,
SUBSTRING(M.FValue,N.number,
CHARINDEX(';',M.FValue + ';',N.number) - N.number) AS FValue
FROM (SELECT rowid=ROW_NUMBER() OVER(ORDER BY (SELECT 1)),FValue FROM @tb2) AS M
JOIN master.dbo.spt_values AS N
ON N.type = 'p' AND N.number BETWEEN 1 AND LEN(M.FValue)
AND SUBSTRING(';' + M.FValue,N.number,1) = ';'
) AS A
JOIN @tb1 AS B
ON A.FValue = B.FValue
)
SELECT
MAX(CASE WHEN rnk = 1 THEN STUFF(xmldoc.value('.','varchar(20)'),1,1,'') ELSE '' END) AS F1,
MAX(CASE WHEN rnk = 2 THEN STUFF(xmldoc.value('.','varchar(20)'),1,1,'') ELSE '' END) AS F2
FROM
(
SELECT
*,rnk=ROW_NUMBER() OVER(PARTITION BY rowid ORDER BY ID)
FROM
(
SELECT DISTINCT rowid,ID FROM LiangLoveLan
) AS A
OUTER APPLY
(
SELECT xmldoc = (
SELECT ',' + FValue AS [text()]
FROM LiangLoveLan
WHERE rowid = A.rowid AND ID = A.ID
FOR XML PATH(''),TYPE
)
) AS B
) AS T
GROUP BY rowid
ORDER BY rowid;/*
F1 F2
-------------------- --------------------
01,02,03 04
02 04,05
01,03 05,06
01 07(4 行受影响)*/
insert A
select 1 ,'01'
union select 1 ,'02'
union select 1 ,'03'
union select 2 ,'04'
union select 2 ,'05'
union select 2 ,'06'
union select 3 ,'07'
union select 3 ,'08'
union select 4 ,'09'
union select 4 ,'10'
create table B (FValue varchar(100))
insert B
select '01;02;03;04'
union select '02;04;05'
union select '01;03;05;06'
union select '01;07'
union select '05;09;10'
go
go
create function f(@id varchar(1000),@flag int)
returns varchar(1000)
as
begin
declare @str varchar(1000) select @str=isnull(@str,'')+';'+FValue from (select f1=b.fvalue,id,a.FValue from B inner join A on charindex(a.FValue,b.FValue)>0) t where f1 = @id and id = @flag
return stuff(@str,1,1,'')end
goselect distinct
dbo.f(t.fvalue,(select min(id) from B inner join A on charindex(a.FValue,b.FValue)>0 where b.fvalue = t.fvalue group by b.fvalue)),
dbo.f(t.fvalue,(select max(id) from B inner join A on charindex(a.FValue,b.FValue)>0 where b.fvalue = t.fvalue group by b.fvalue))
from B t inner join A on charindex(a.FValue,t.FValue)>0 group by t.fvalue,id
drop table A,B
drop function f
/*B表如何得到如下结果:
F1 F2
01 07
01;02;03 04
01;03 05;06
02 04;05
05 09;10
*/
if not object_id('A') is null
drop table A
go
CREATE TABLE [dbo].[A](
[ID] [int] NULL,
[FValue] [varchar](2) NULL
)
if not object_id('B') is null
drop table B
go
create table B(
FValueB varchar(1000)
)
go
insert into A select
1, '01' union all select
1, '02' union all select
1, '03' union all select
2, '04' union all select
2, '05' union all select
2, '06'
go
insert into B select
'01;02;03;04' union all select
'02;04;05' union all select
'01;03;05;06' alter function f_GetStr(@Id int, @val varchar(1000))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s = ''
select @s = @s+','+FValue from A where ID = @Id and charindex(FValue, @val) > 0
return stuff(@s, 1, 1, '')
endselect F1 = dbo.f_GetStr(1, FValueB), F2 = dbo.f_GetStr(2, FValueB) from B