sql 方法（长期更新中）

来源：互联网发布：java程序设计上机实验编辑：程序博客网时间：2024/05/01 14:22

分拆列值

原著：邹建改编：爱新觉罗.毓华 2007-12-16 广东深圳

有表tb, 如下:id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc

1. 旧的解决方法(sql server 2000)SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b

SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[values], B.id, 1) = ','

DROP TABLE #

2. 新的解决方法(sql server 2005)

create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')goSELECT A.id, B.valueFROM( SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb)AOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v))B

DROP TABLE tb

/*id value----------- ------------------------------1 aa1 bb2 aaa2 bbb2 ccc

(5 行受影响)*/

表 product

id Pname Pcolor 1 p1 1,2,3 2 p2 2,3 3 p3 1,2,3 4 p4 1,2

现在想得到这么一个结果

colornum colorId

3 1 4 2 3 3

create table product (id int,Pname varchar(2),Pcolor varchar(200))insert into productselect 1,'p1','1,2,3' union allselect 2,'p2','2,3' union allselect 3,'p3','1,2,3' union allselect 4,'p4','1,2'

--查询declare @s varchar(max) set @s=''select @s=@s+Pcolor+',' from productif len(@s)>0 set @s=left(@s,len(@s)-1)select count(*)as colornum ,a as colorId from f_split(@s,',') group by a order by a

/*-------------------3 14 23 3*/

--送你split函数create function f_split(@str varchar(8000),@strseprate varchar(1)) returns @temp table(id int identity(1,1),a varchar(100)) as begin declare @i int set @str=rtrim(ltrim(@str)) set @i=charindex(@strseprate,@str) while @i>=1 begin insert @temp values(left(@str,@i-1)) set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为stuff,可以自己试着改写一下 set @i=charindex(@strseprate,@str) end if @str<>'/' insert @temp values(@str) return end