34,594
社区成员
发帖
与我相关
我的任务
分享
--參考老大的函數
--> Title : Generating test data [ta]
--> Author :
--> Date : 2009-11-17 15:57:09
if object_ID('[tb]') is not null drop table [tb]
go
create table [tb] (ID nvarchar(4),col varchar(20))
insert into [tb]
select N'张三','1,2,3' union all
select N'李四','2,3' union all
select N'张三','1,2,4,5' union all
select N'李四','2,3'
IF OBJECT_ID('dbo.f_mergSTR')IS NOT NULL DROP FUNCTION dbo.f_mergSTR
GO
CREATE FUNCTION dbo.f_mergSTR(@ID nvarchar(20))
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns
DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
)a ORDER BY s
RETURN(STUFF(@r,1,1,''))
END
GO
--调用用户定义实现交并集查询
SELECT ID,col=dbo.f_mergSTR(ID)
FROM tb
GROUP BY ID
GO
/*
ID col
---- --------------------------------------------------
李四 2,3
张三 1,2,3,4,5
(2 個資料列受到影響)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-17 15:55:07
-- 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]([用户] varchar(4),[中间结果] varchar(7))
insert [tb]
select '张三','1,2,3' union all
select '李四','2,3' union all
select '张三','1,2,4,5' union all
select '李四','2,3'
--------------开始查询--------------------------
;with f as
(
select
a.用户,b.中间结果
from
(select 用户,中间结果=convert(xml,'<root><v>'+replace(中间结果,',','</v><v>')+'</v></root>') from Tb)a
outer apply
(select 中间结果=C.v.value('.','nvarchar(100)') from a.中间结果.nodes('/root/v')C(v))b
)
select 用户, [中间结果]=stuff((select ','+[中间结果] from (select distinct * from f)a where 用户=t.用户 for xml path('')), 1, 1, '')
from (select distinct * from f)t
group by 用户
----------------结果----------------------------
/*用户 中间结果
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四 2,3
张三 1,2,3,4,5
(2 行受影响)
*/