select ID,名称, 性质 = (select 性质 + '' from table2 where ID = a.ID for xml path('') ), 功能 = (select 功能 + '' from table3 where ID = a.ID for xml path('') )from table1 group by ID 没有测试,你试一下先!~
select ID,名称, 性质 = stuff( (select 性质 + '' from table2 where ID = a.ID for xml path(''),1,1,'' ), 功能 = stuff((select 功能 + '' from table3 where ID = a.ID for xml path(''),1,1,'' )from table1 group by ID,名称
create table table1 (id int, 名称 nvarchar(20)) create table table2 (id int,性质 nvarchar(20)) create table table3 (id int ,功能 nvarchar(20)) insert into table1 select 1,'名1'insert into table2 select 1, '性质1' union all select 1, '性质2' union all select 1, '性质3' insert into table3 select 1, '功能1' union all select 1, '功能2' union all select 1, '功能3'select ID,名称, 性质 = stuff((select ',' + 性质 + '' from table2 where ID = a.ID for xml path('') ), 1,1,''), 功能 = stuff((select ',' +功能 + '' from table3 where ID = a.ID for xml path('')) ,1,1,'')from table1 a group by ID,名称ID 名称 性质 功能 ----------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 名1 性质1,性质2,性质3 功能1,功能2,功能3(1 row(s) affected)
2000,我是用函数~~create function fn_get性质(@id int) returns nvarchar(100) as begin declare @str nvarchar(100) set @str = ''select @str = @str +',' +性质 from table2 where ID = @id return stuff(@str,1,1,'')endcreate function fn_get功能(@id int) returns nvarchar(100) as begin declare @str nvarchar(100) set @str = ''select @str = @str +',' +功能 from table3 where ID = @id return stuff(@str,1,1,'')endselect ID,dbo.fn_get性质(id),dbo.fn_get功能(id) from table1 ID ----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 1 性质1,性质2,性质3 功能1,功能2,功能3(1 row(s) affected)
SQL2000下 create table table1 (id int, 名称 nvarchar(20)) create table table2 (id int,性质 nvarchar(250)) create table table3 (id int ,功能 nvarchar(250)) insert into table1 select 1,'名1'insert into table2 select 1, '性质1' union all select 1, '性质2' union all select 1, '性质3'insert into table3 select 1, '功能1' union all select 1, '功能2' union all select 1, '功能3' godeclare @id int, @xz varchar(250), @gn varchar(250) UPDATE table2 SET @xz = CASE WHEN @id = id THEN @xz + ',' + rtrim(性质) ELSE rtrim(性质) END, @id = id, 性质 = @xz set @id = null UPDATE table3 SET @gn = CASE WHEN @id = id THEN @gn + ',' + rtrim(功能) ELSE rtrim(功能) END, @id = id, 功能 = @gnselect table1.id, 名称, max(性质) as 性质, max(功能) as 功能 from table1 join table2 on table1.id = table2.id join table3 on table1.id = table3.id group by table1.id, 名称 godrop table table1 drop table table2 drop table table3 go/* id 名称 性质 功能 ----------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 名1 性质1,性质2,性质3 功能1,功能2,功能3(所影响的行数为 1 行) */
select ID,名称,
性质 = stuff( (select 性质 + '' from table2 where ID = a.ID for xml path(''),1,1,'' ),
功能 = stuff((select 功能 + '' from table3 where ID = a.ID for xml path(''),1,1,'' )from table1
group by ID,名称
create table table1
(id int, 名称 nvarchar(20))
create table table2
(id int,性质 nvarchar(20))
create table table3
(id int ,功能 nvarchar(20))
insert into table1
select 1,'名1'insert into table2
select 1, '性质1'
union all
select 1, '性质2'
union all
select 1, '性质3'
insert into table3
select 1, '功能1'
union all
select 1, '功能2'
union all
select 1, '功能3'select ID,名称,
性质 = stuff((select ',' + 性质 + '' from table2 where ID = a.ID for xml path('') ), 1,1,''),
功能 = stuff((select ',' +功能 + '' from table3 where ID = a.ID for xml path('')) ,1,1,'')from table1 a
group by ID,名称ID 名称 性质 功能
----------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 名1 性质1,性质2,性质3 功能1,功能2,功能3(1 row(s) affected)
服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'xml' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 3
在关键字 'for' 附近有语法错误。
我也没看明白。xml那是什么意思?
2000下怎么做呢
2000,我是用函数~~create function fn_get性质(@id int)
returns nvarchar(100)
as
begin declare @str nvarchar(100)
set @str = ''select @str = @str +',' +性质 from table2 where ID = @id
return stuff(@str,1,1,'')endcreate function fn_get功能(@id int)
returns nvarchar(100)
as
begin declare @str nvarchar(100)
set @str = ''select @str = @str +',' +功能 from table3 where ID = @id
return stuff(@str,1,1,'')endselect ID,dbo.fn_get性质(id),dbo.fn_get功能(id) from table1
ID
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 性质1,性质2,性质3 功能1,功能2,功能3(1 row(s) affected)
create table table1
(id int, 名称 nvarchar(20))
create table table2
(id int,性质 nvarchar(250))
create table table3
(id int ,功能 nvarchar(250))
insert into table1
select 1,'名1'insert into table2
select 1, '性质1'
union all
select 1, '性质2'
union all
select 1, '性质3'insert into table3
select 1, '功能1'
union all
select 1, '功能2'
union all
select 1, '功能3'
godeclare @id int, @xz varchar(250), @gn varchar(250)
UPDATE table2 SET @xz = CASE WHEN @id = id THEN @xz + ',' + rtrim(性质)
ELSE rtrim(性质) END, @id = id, 性质 = @xz
set @id = null
UPDATE table3 SET @gn = CASE WHEN @id = id THEN @gn + ',' + rtrim(功能)
ELSE rtrim(功能) END, @id = id, 功能 = @gnselect table1.id, 名称, max(性质) as 性质, max(功能) as 功能 from table1 join table2 on table1.id = table2.id join table3 on table1.id = table3.id group by table1.id, 名称
godrop table table1
drop table table2
drop table table3
go/*
id 名称 性质 功能
----------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 名1 性质1,性质2,性质3 功能1,功能2,功能3(所影响的行数为 1 行)
*/