表1类型表:Type
ID Type
1 A1
2 A2
3 A3
表2:详情表:Details
ID Type Dtext
1 A1 计算机
2 A1 外语
3 A1 数学
4 A2 体育
5 A2 艺术
6 A3 法律
7 A3 政治
根据两张表,需要查询出的最终结果为:
Type Dtext
A1 计算机, 外语, 数学
A2 体育, 艺术
A3 法律, 政治
请问SQl语句该如何写????
ID Type
1 A1
2 A2
3 A3
表2:详情表:Details
ID Type Dtext
1 A1 计算机
2 A1 外语
3 A1 数学
4 A2 体育
5 A2 艺术
6 A3 法律
7 A3 政治
根据两张表,需要查询出的最终结果为:
Type Dtext
A1 计算机, 外语, 数学
A2 体育, 艺术
A3 法律, 政治
请问SQl语句该如何写????
go
if object_id('test.dbo.Type') is not null drop table Type
-- 创建数据表
create table Type
(
ID int,
Type char(3)
)
go
--插入测试数据
insert into Type select 1,'A1'
union all select 2,'A2'
union all select 3,'A3'
union all select 4,'A4'
go
if object_id('test.dbo.Details') is not null drop table Details
-- 创建数据表
create table Details
(
ID int,
Type char(3),
Dtext char(7)
)
go
--插入测试数据
insert into Details select 1,'A1','计算机'
union all select 2,'A1','外语'
union all select 3,'A1','数学'
union all select 4,'A2','体育'
union all select 5,'A2','艺术'
union all select 6,'A3','法律'
union all select 7,'A3','政治'
go
--代码实现--方法一:
select a.Type,b.Dtext from Type a left join (
select distinct Type,Dtext=stuff((select ','+rtrim(Dtext) from Details where Type=t.Type for xml path('')),1,1,'')
from Details t ) b on a.Type=b.Type--方法二:
if object_id('test.dbo.f_str') is not null drop function f_str
go
create function dbo.f_str(@Type varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(Dtext as varchar) from Details where Type = @Type
return @str
end
go
--调用函数
select a.Type,b.Dtext from Type a left join (
select distinct Type,Dtext=dbo.f_str(Type)
from Details t ) b on a.Type=b.Type/*测试结果Type Dtext
---------------------
A1 计算机,外语,数学
A2 体育,艺术
A3 法律,政治
A4 NULL --加了行测试数据(4 行受影响)
*/
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳描述:将如下形式的数据按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb
-- 创建数据表
create table Type
(
ID int,
Type char(3)
)
go
--插入测试数据
insert into Type select 1,'A1'
union all select 2,'A2'
union all select 3,'A3'
union all select 4,'A4'
go
if object_id('test.dbo.Details') is not null drop table Details
-- 创建数据表
create table Details
(
ID int,
Type char(3),
Dtext char(7)
)
go
--插入测试数据
insert into Details select 1,'A1','计算机'
union all select 2,'A1','外语'
union all select 3,'A1','数学'
union all select 4,'A2','体育'
union all select 5,'A2','艺术'
union all select 6,'A3','法律'
union all select 7,'A3','政治'
go
--合并字段
if object_id('f_str')is not null drop function f_str
go
create function f_str(@Type char(3))
returns varchar(400)
as
begin
declare @str varchar(400)
select @str=isnull(@str+',','')+d.Dtext
from Details d where d.Type=@Type return @str
end
go
select Type,Dtext=dbo.f_str(Type) from Details d
group by Type
go
--测试结果
Type Dtext
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A1 计算机 ,外语 ,数学
A2 体育 ,艺术
A3 法律 ,政治
on t.Type=d.Type
group by t.Type
go
既然要分表就分清楚
details表里引用type表的主键就行了
楼主要的结果用一个details表就可以实现了