---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-30 17:08:47 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[bm] varchar(3)) insert [tb] select 1,'001' union all select 1,'002' union all select 2,'003' union all select 2,'004' --------------开始查询-------------------------- select id, [bm]=stuff((select '/'+[bm] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id ----------------结果---------------------------- /* id bm ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 001/002 2 003/004(2 行受影响)*/
..2000函数 2005 XML PATH
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-30 17:08:47 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[bm] varchar(3)) insert [tb] select 1,'001' union all select 1,'002' union all select 2,'003' union all select 2,'004' --------------开始查询-------------------------- CREATE FUNCTION dbo.f_strUnite(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @str varchar(8000) SET @str = '' SELECT @str = @str + '/' + bm 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 bm ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 001/002 2 003/004(2 行受影响)*/
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[bm] varchar(3)) insert [tb] select 1,'001' union all select 1,'002' union all select 2,'003' union all select 2,'004'select [id], name=MAX(case when rn=1 then bm else '' end)+MAX(case when rn=2 then '/'+bm else '' end) +MAX(case when rn=3 then '/'+bm else '' end)+MAX(case when rn=4 then '/'+bm else '' end) from( select [id],[bm], (select COUNT(*) from tb where k.id=id and k.[bm]>=[bm]) as rn from tb k )z group by id(4 行受影响) id name ----------- --------------- 1 001/002 2 003/004 纯属练手 不适用
--测试数据 if object_id('aa') is not null drop table aa go create table aa(id int,bm varchar(20)) insert aa select 1,'001' union all select 1,'002' union all select 2,'003' union all select 2,'004'--组合字符串 if object_id('strfun') is not null drop function strfun go create function strfun(@idd int) returns varchar(1000) as begin declare @mm varchar(1000) select @mm=isnull(@mm+'/','')+bm from aa where id=@idd return @mm end--查询 select id,bm=dbo.strfun(id) from aa group by id
if object_id(N'A') is not null drop table A create table A(id int,bm nvarchar(10)) insert into A(id,bm) select 1,'001' union all select 1,'002' union all select 2,'003' union all select 2,'004' go--declare @id int,@bm nvarchar(20) --update A set @bm=case when @id is null or @id<> id then bm else @bm+bm end, -- @id=id, -- bm=@bm select a1.id,a1.bm+'/'+a2.bm from A a1 left join (select * from A )a2 on a2.id=a1.id and a2.bm>a1.bm where a1.bm+'/'+a2.bm is not null
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-30 17:08:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[bm] varchar(3))
insert [tb]
select 1,'001' union all
select 1,'002' union all
select 2,'003' union all
select 2,'004'
--------------开始查询--------------------------
select id, [bm]=stuff((select '/'+[bm] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id ----------------结果----------------------------
/* id bm
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 001/002
2 003/004(2 行受影响)*/
2005 XML PATH
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-30 17:08:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[bm] varchar(3))
insert [tb]
select 1,'001' union all
select 1,'002' union all
select 2,'003' union all
select 2,'004'
--------------开始查询--------------------------
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + '/' + bm 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 bm
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 001/002
2 003/004(2 行受影响)*/
drop table [tb]
go
create table [tb]([id] int,[bm] varchar(3))
insert [tb]
select 1,'001' union all
select 1,'002' union all
select 2,'003' union all
select 2,'004'select
[id],
name=MAX(case when rn=1 then bm else '' end)+MAX(case when rn=2 then '/'+bm else '' end)
+MAX(case when rn=3 then '/'+bm else '' end)+MAX(case when rn=4 then '/'+bm else '' end)
from(
select [id],[bm],
(select COUNT(*) from tb where k.id=id and k.[bm]>=[bm]) as rn
from tb k )z
group by id(4 行受影响)
id name
----------- ---------------
1 001/002
2 003/004
纯属练手
不适用
--测试数据
if object_id('aa') is not null drop table aa
go
create table aa(id int,bm varchar(20))
insert aa
select 1,'001' union all
select 1,'002' union all
select 2,'003' union all
select 2,'004'--组合字符串
if object_id('strfun') is not null drop function strfun
go
create function strfun(@idd int)
returns varchar(1000)
as
begin
declare @mm varchar(1000)
select @mm=isnull(@mm+'/','')+bm
from aa where id=@idd
return @mm
end--查询
select id,bm=dbo.strfun(id) from aa group by id
create table A(id int,bm nvarchar(10))
insert into A(id,bm)
select 1,'001' union all
select 1,'002' union all
select 2,'003' union all
select 2,'004'
go--declare @id int,@bm nvarchar(20)
--update A set @bm=case when @id is null or @id<> id then bm else @bm+bm end,
-- @id=id,
-- bm=@bm
select a1.id,a1.bm+'/'+a2.bm from A a1 left join (select * from A )a2 on a2.id=a1.id and a2.bm>a1.bm where a1.bm+'/'+a2.bm is not null