1.创建表脚本
CREATE TABLE [dbo].[test](
[factroy] [nvarchar](255) NULL,
[rawnum] [varchar](255) NULL,
[merge_fac] [nvarchar](255) NULL
) ON [PRIMARY]2.添加数据
insert into test values('ibmfac1','112120000',null)
insert into test values('ibmfac1','112120001',null)
insert into test values('ibmfac2','112120000',null)
insert into test values('ibmfac3','112120005',null)
insert into test values('ibmfac2','112120004',null)
insert into test values('ibmfac1','112120007',null)
insert into test values('ibmfac4','112120006',null)
insert into test values('ibmfac2','112120005',null)3.需求说明a 一个材料号可以对应多个工厂,如有多工厂,就合并到merge_fac列【格式化为 ibmfac1;ibmfac2】字符串;
b 如果工厂列和材料号列都不重复原样输出;
4.结果显示应为:factroy rawnum merge_fac
ibmfac1 112120000 ibmfac1;ibmfac2
ibmfac1 112120001 ibmfac1
ibmfac3 112120005 ibmfac3;ibmfac2
ibmfac2 112120004 ibmfac2
ibmfac1 112120007 ibmfac1
ibmfac4;ibmfac1 112120006 ibmfac4;ibmfac1
5.求select ,cursor,function,不限。多方法求解。祝大家春节愉快!
CREATE TABLE [dbo].[test](
[factroy] [nvarchar](255) NULL,
[rawnum] [varchar](255) NULL,
[merge_fac] [nvarchar](255) NULL
) ON [PRIMARY]2.添加数据
insert into test values('ibmfac1','112120000',null)
insert into test values('ibmfac1','112120001',null)
insert into test values('ibmfac2','112120000',null)
insert into test values('ibmfac3','112120005',null)
insert into test values('ibmfac2','112120004',null)
insert into test values('ibmfac1','112120007',null)
insert into test values('ibmfac4','112120006',null)
insert into test values('ibmfac2','112120005',null)3.需求说明a 一个材料号可以对应多个工厂,如有多工厂,就合并到merge_fac列【格式化为 ibmfac1;ibmfac2】字符串;
b 如果工厂列和材料号列都不重复原样输出;
4.结果显示应为:factroy rawnum merge_fac
ibmfac1 112120000 ibmfac1;ibmfac2
ibmfac1 112120001 ibmfac1
ibmfac3 112120005 ibmfac3;ibmfac2
ibmfac2 112120004 ibmfac2
ibmfac1 112120007 ibmfac1
ibmfac4;ibmfac1 112120006 ibmfac4;ibmfac1
5.求select ,cursor,function,不限。多方法求解。祝大家春节愉快!
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @CLASS VARCHAR(50)
SELECT @CLASS=ISNULL(@CLASS+'/','')+LTRIM(B) FROM T WHERE A=@ID
RETURN @CLASS
END
2000R 函数
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加) 1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
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
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
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
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法2
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 select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb /*
[factroy] [nvarchar](255) NULL,
[rawnum] [varchar](255) NULL,
[merge_fac] [nvarchar](255) NULL
) ON [PRIMARY] insert into test values('ibmfac1','112120000',null)
insert into test values('ibmfac1','112120001',null)
insert into test values('ibmfac2','112120000',null)
insert into test values('ibmfac3','112120005',null)
insert into test values('ibmfac2','112120004',null)
insert into test values('ibmfac1','112120007',null)
insert into test values('ibmfac4','112120006',null)
insert into test values('ibmfac2','112120005',null)--select * from testif object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+factroy from Test where rawnum=@Col1
return @S
end
go Select distinct rawnum,factroy=dbo.F_Str(rawnum) from Testgo drop table test
/*rawnum factroy
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
112120000 ibmfac1,ibmfac2
112120001 ibmfac1
112120004 ibmfac2
112120005 ibmfac3,ibmfac2
112120006 ibmfac4
112120007 ibmfac1(6 行受影响)
*/
CREATE FUNCTION f_Test(@rawnum int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + factroy FROM test WHERE rawnum=@rawnum
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECT rawnum, value = dbo.f_Test(rawnum) FROM test GROUP BY rawnum drop table tb
drop function dbo.f_test
go小F写得就是方法。lz可以参考自己考虑处理一下。
1.如果rawnum列含有不规则字符串,函数应该怎么改写!!!
报错参数错误!
(
@strraw varchar(200)
)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=case when @str='' then @str+factroy else @str+';'+factroy end from test where rawnum=@strraw
return @str
end
goselect distinct rawnum,dbo.Getstrfatory(rawnum) from test
[factroy] [nvarchar](255) NULL,
[rawnum] [varchar](255) NULL,
[merge_fac] [nvarchar](255) NULL
) ON [PRIMARY]
go
set nocount oninsert into test values('ibmfac1','112120000',null)
insert into test values('ibmfac1','112120001',null)
insert into test values('ibmfac2','112120000',null)
insert into test values('ibmfac3','112120005',null)
insert into test values('ibmfac2','112120004',null)
insert into test values('ibmfac1','112120007',null)
insert into test values('ibmfac4','112120006',null)
insert into test values('ibmfac2','112120005',null)
go
select * into lintest from test order by [rawnum]declare @rawnum varchar(100),@merge_fac varchar(100)
select @rawnum='',@merge_fac=''
update lintest
set @merge_fac=case when @rawnum=rawnum then @merge_fac+','+[factroy] else [factroy] end,@rawnum=rawnum,merge_fac=@merge_fac from lintest
select * from lintest
go
drop table test
drop table lintest