求一个sql查询语句

[Millor]

A， id, attributes
1， 1001,12;1002,45
2, 1002,14;1003,54
B, id,name
1001,无敌
1002，弱
1003，强悍
A中attributes字段是有多个B数据表的id和值组成的，

现在我想在select,的时候把他的name和值一起显示；
1，无敌,12;弱,45
2, 弱,14;强悍,54

语句该怎么写

[黄_瓜]

left join 

[htl258_Tony]

--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
	DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[attributes] [nvarchar](20))
INSERT INTO [A]
SELECT '1','1001,12;1002,45' UNION ALL
SELECT '2','1002,14;1003,54'

--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
	DROP TABLE [B]
GO
CREATE TABLE [B] ([id] [int],[name] [nvarchar](10))
INSERT INTO [B]
SELECT '1001','无敌' UNION ALL
SELECT '1002','弱' UNION ALL
SELECT '1003','强悍'

--SELECT * FROM [A]
--SELECT * FROM [B]

-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@S VARCHAR(200)
)RETURNS VARCHAR(200)
AS
BEGIN
	SELECT @S=REPLACE(@S,LTRIM([id]),NAME) FROM B
	RETURN @S
END
GO

SELECT id,[attributes]=dbo.[fn_test]([attributes]) FROM A
/*
id          attributes
----------- -----------------------
1           无敌,12;弱,45
2           弱,14;强悍,54

(2 行受影响)
*/

[老黎]

字符串分割，up

[htl258_Tony]

--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
	DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[attributes] [nvarchar](20))
INSERT INTO [A]
SELECT '1','1001,12;1002,45' UNION ALL
SELECT '2','1002,14;1003,54'

--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
	DROP TABLE [B]
GO
CREATE TABLE [B] ([id] [int],[name] [nvarchar](10))
INSERT INTO [B]
SELECT '1001','无敌' UNION ALL
SELECT '1002','弱' UNION ALL
SELECT '1003','强悍'

--SELECT * FROM [A]
--SELECT * FROM [B]

-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@S VARCHAR(200)
)RETURNS VARCHAR(200)
AS
BEGIN
	SELECT @S=REPLACE(@S,LTRIM([id])+',',NAME+',') FROM B
	RETURN @S
END
GO

SELECT id,[attributes]=dbo.[fn_test]([attributes]) FROM A
/*
id          attributes
----------- -----------------------
1           无敌,12;弱,45
2           弱,14;强悍,54

(2 行受影响)
*/

这样妥些

[黄_瓜]

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[attributes] varchar(15))
insert [ta]
select 1,'1001,12;1002,45' union all
select 2,'1002,14;1003,54'

--------------------------------查询开始------------------------------

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4))
insert [tb]
select 1001,'无敌' union all
select 1002,'弱' union all
select 1003,'强悍'

--------------------------------查询开始------------------------------
if object_id('[f]') is not null drop function [f]
go
create function [f](
@s varchar(20)
)returns varchar(20)
as
begin
    select @s=replace(@s,ltrim([id])+',',name+',') from tb
    return @s
end
go
select a.id, name=dbo.f([attributes])  from [ta] a
/*
id          name
----------- --------------------
1           无敌,12;弱,45
2           弱,14;强悍,54

(2 行受影响)

*/

[黄_瓜]

1楼的看错了。。。。。。。。。。。。。。。

[yuyujulin]

 SELECT @S=REPLACE(@S,LTRIM([id])+',',NAME+',') FROM B

妙啊！

[crszf]

[Quote=引用 4 楼 htl258 的回复:]
SQL code
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[attributes] [nvarchar](20))
INSERT INTO [A]
SELECT '1','1001,12;1002,45' UNION ALL
……
[/Quote]

UP

[strive_bo]

IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[attributes] [nvarchar](20))
INSERT INTO [A]
SELECT '1','1001,12;1002,45' UNION ALL
SELECT '2','1002,14;1003,54'

--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([id] [int],[name] [nvarchar](10))
INSERT INTO [B]
SELECT '1001','无敌' UNION ALL
SELECT '1002','弱' UNION ALL
SELECT '1003','强悍'

--SELECT * FROM [A]
--SELECT * FROM [B]

-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@S VARCHAR(200)
)RETURNS VARCHAR(200)
AS
BEGIN
SELECT @S=REPLACE(@S,LTRIM([id])+',',NAME+',') FROM B
RETURN @S
END
GO

SELECT id,[attributes]=dbo.[fn_test]([attributes]) FROM A



支持一下。。。

[sych888]

[Quote=引用 5 楼 beirut 的回复:]
SQL code
--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[attributes] varchar(15))
insert [ta]
select 1,'1001,12;1002,45' union all
select 2,'1002,1……
[/Quote]
学习.........

[fengxingphp]

赚个分

[jiang5311]

SELECT @S=REPLACE(@S,LTRIM([id])+',',NAME+',') FROM B

[rfq]

学习了

[hbjlwhl]

[Quote=引用 5 楼 beirut 的回复:]
SQL code
--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[attributes] varchar(15))
insert [ta]
select 1,'1001,12;1002,45' union all
select 2,'1002,1……
[/Quote] up

[yjwcwrkks]

学习....

[cftzh]

学习ing

[lgt1982]

二楼的果然强！