tab1
pid pname
1 层板
2 顶板
3 中侧板tab2
id pid pcode
1 1 993029
2 1 993030
3 1 9930314 2 311021
5 2 3110226 3 223345
7 3 223346
8 3 223347
9 3 223348MS SQL2000中,如何写一个SQL语句,执行后,查询结果如下:pid pname allcode
1 层板 993029,993030,993031
2 顶板 311021,311022
3 中侧板 223345,223346,223347,223348
create table tab1(pid int, pname varchar(100));insert into tab1
select 1, '层板' union all
select 2, '顶板' union all
select 3, '中侧板'create table tab2(id int, pid int, pcode varchar(100))insert into tab2
select 1, 1, '993029' union all
select 2, 1, '993030' union all
select 3, 1, '993031' union all
select 4, 2, '311021' union all
select 5, 2, '311022' union all
select 6, 3, '223345' union all
select 7, 3, '223346' union all
select 8, 3, '223347' union all
select 9, 3, '223348'
select distinct
t1.pid,
t1.pname,
stuff((select ',' + t2.pcode
from tab2 t2
where t2.pid = t1.pid
for xml path('')
),1,1,''
) as pcode
from tab1 t1
/*
pid pname pcode
1 层板 993029,993030,993031
2 顶板 311021,311022
3 中侧板 223345,223346,223347,223348
*/
create table tab1(pid int, pname varchar(100));insert into tab1
select 1, '层板' union all
select 2, '顶板' union all
select 3, '中侧板'create table tab2(id int, pid int, pcode varchar(100))insert into tab2
select 1, 1, '993029' union all
select 2, 1, '993030' union all
select 3, 1, '993031' union all
select 4, 2, '311021' union all
select 5, 2, '311022' union all
select 6, 3, '223345' union all
select 7, 3, '223346' union all
select 8, 3, '223347' union all
select 9, 3, '223348'
go
--drop function dbo.fn_mergeSTr
create function dbo.fn_mergeSTR(@pid int,@split varchar(10))
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
select @str = @str + pcode + @split
from tab2
where pid = @pid
set @str = left(@str , len(@str) - LEN(@split) )
return @str --返回值
end
go
select distinct
t1.pid,
t1.pname,
dbo.fn_mergeSTR(t1.pid,',') as str
from tab1 t1
/*
pid pname pcode
1 层板 993029,993030,993031
2 顶板 311021,311022
3 中侧板 223345,223346,223347,223348
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-18 20:51:27
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[tab1]
if object_id('[tab1]') is not null drop table [tab1]
go
create table [tab1]([pid] int,[pname] varchar(6))
insert [tab1]
select 1,'层板' union all
select 2,'顶板' union all
select 3,'中侧板'
--> 测试数据:[tab2]
if object_id('[tab2]') is not null drop table [tab2]
go
create table [tab2]([id] int,[pid] int,[pcode] VARCHAR(10))
insert [tab2]
select 1,1,993029 union all
select 2,1,993030 union all
select 3,1,993031 union all
select 4,2,311021 union all
select 5,2,311022 union all
select 6,3,223345 union all
select 7,3,223346 union all
select 8,3,223347 union all
select 9,3,223348
--------------开始查询--------------------------
--创建函数来显示
go
CREATE FUNCTION f_hb ( @id VARCHAR(10) )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @str VARCHAR(1000)
SET @str = ''
SELECT @str = @str + ',' + [pcode]
FROM [tab2]
WHERE CHARINDEX(',' + CAST(pid AS VARCHAR(256)) + ',',
',' + @id + ',') > 0
RETURN STUFF(@str,1,1,'')
END
go
SELECT [pid] ,
[pname] ,
classid = dbo.f_hb(pid)
FROM [tab1]
go
DROP FUNCTION dbo.f_hb
----------------结果----------------------------
/*
pid pname classid
----------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 层板 993029,993030,993031
2 顶板 311021,311022
3 中侧板 223345,223346,223347,223348
*/