字符串拆分问题

2009-05-13 10:47:13 161 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

从零到飞：在Ubuntu 22.04上为你的无人机搭建PX4-Autopilot开发环境本文详细指导在Ubuntu 22.04上搭建PX4-Autopilot开发环境，涵盖系统依赖安装、源码获取、编译目标选择及Gazebo仿真测试。通过优化构建过程和调试技巧，帮助开发者快速为无人机部署稳定的飞控系统，实现从仿真到实机的平滑过渡。阅读详情



USE TEMPDB

GO

--建表

IF OBJECT_ID('ARRAYS','U') IS NOT NULL

DROP TABLE ARRAYS

GO

CREATE TABLE ARRAYS

(ARRAYID VARCHAR(10) NOT NULL,ARRAYVALUE VARCHAR(100) NOT NULL)

GO

INSERT INTO ARRAYS

SELECT 'A','20,22,25,25,14' UNION ALL

SELECT 'B','30,33,28' UNION ALL

SELECT 'C','12,10,8,12,12,13,12,14,10,9' UNION ALL

SELECT 'D','-4,-5,-6,-4,-2'

GO

--需要得到的结果

ARRAYID	ELEMENT

A	20

A	22

A	25

A	25

A	14

B	30

B	33

B	28

C	12

C	10

C	8

C	12

C	12

C	13

C	12

C	14

C	10

D	-4

D	-5

D	-6

D	-4

D	-2

请不要用存储过程,函数,游标.各位牛牛.怎么写这个SQL?

...全文

Linux蓝牙开发实战：5分钟搞懂BlueZ与D-Bus通信原理（附D-Feet调试技巧）本文深入解析Linux下BlueZ蓝牙协议栈与D-Bus通信的核心机制，提供实战开发指南。通过D-Bus对象模型、信号订阅等关键技术点的详细讲解，配合D-Feet工具的高效调试技巧，帮助开发者快速掌握蓝牙设备管理与性能优化方法，提升物联网开发效率。阅读详情

dpzc_love

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

dpzc_love 2009-05-13

打赏
举报

呵呵.赞!结贴

ai_li7758521 2009-05-13

打赏
举报

分拆列值 



原著：邹建 

改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳 



有表tb, 如下: 

id          value 

----------- ----------- 

1          aa,bb 

2          aaa,bbb,ccc 

欲按id,分拆value列, 分拆后结果如下: 

id          value 

----------- -------- 

1          aa 

1          bb 

2          aaa 

2          bbb 

2          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, # B 

WHERE 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') 

go 

SELECT A.id, B.value 

FROM( 

    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 

)A 

OUTER APPLY( 

    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) 

)B 



DROP TABLE tb 



/* 

id          value 

----------- ------------------------------ 

1          aa 

1          bb 

2          aaa 

2          bbb 

2          ccc 



(5 行受影响) 

*/

--小F-- 2009-05-13

打赏
举报

眼睛都看花了学习下

liangCK 2009-05-13

打赏
举报

USE TEMPDB

GO

--建表

IF OBJECT_ID('ARRAYS','U') IS NOT NULL

DROP TABLE ARRAYS

GO

CREATE TABLE ARRAYS

(ARRAYID VARCHAR(10) NOT NULL,ARRAYVALUE VARCHAR(100) NOT NULL)

GO

INSERT INTO ARRAYS

SELECT 'A','20,22,25,25,14' UNION ALL

SELECT 'B','30,33,28' UNION ALL

SELECT 'C','12,10,8,12,12,13,12,14,10,9' UNION ALL

SELECT 'D','-4,-5,-6,-4,-2'

GO



SELECT

    A.ARRAYID,

    SUBSTRING(A.ARRAYVALUE,B.number,CHARINDEX(',',A.ARRAYVALUE+',',B.number)-B.number) AS ARRAYVALUE

FROM ARRAYS AS A

    JOIN master.dbo.spt_values AS B

ON B.type='p'

    AND B.number BETWEEN 1 AND LEN(A.ARRAYVALUE)

    AND SUBSTRING(','+A.ARRAYVALUE,B.number,1)=',';



DROP TABLE ARRAYS;



/*

ARRAYID    ARRAYVALUE

---------- ----------------------------------------------------------------------------------------------------

A          20

A          22

A          25

A          25

A          14

B          30

B          33

B          28

C          12

C          10

C          8

C          12

C          12

C          13

C          12

C          14

C          10

C          9

D          -4

D          -5

D          -6

D          -4

D          -2



(23 row(s) affected)





*/

liangCK 2009-05-13

打赏
举报



SELECT

    A.ARRAYID,

    SUBSTRING(A.ARRAYVALUE,B.number,CHARINDEX(',',A.ARRAYVALUE+',',B.number)-B.number) AS ARRAYVALUE

FROM ARRAYS AS A

    JOIN master.dbo.spt_values AS B

ON B.type='p'

    AND B.number BETWEEN 1 AND LEN(A.ARRAYVALUE)

    AND SUBSTRING(','+A.ARRAYVALUE,B.number,1)=',';

快乐_石头 2009-05-13

打赏
举报

--合并分拆表

/******************************************************************************************************************************************************

合并分拆表数据



整理人：中国风(Roy)



日期:2008.06.06

******************************************************************************************************************************************************/



--> --> (Roy)生成測試數據

 

if not object_id('Tab') is null

    drop table Tab

Go

Create table Tab([Col1] int,[Col2] nvarchar(1))

Insert Tab

select 1,N'a' union all

select 1,N'b' union all

select 1,N'c' union all

select 2,N'd' union all

select 2,N'e' union all

select 3,N'f'

Go



合并表:



SQL2000用函数:



go

if 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+',','')+Col2 from Tab where Col1=@Col1

    return @S

end

go

Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab



go



SQL2005用XML:



方法1:



select 

    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')

from 

    (select distinct COl1 from Tab) a

Cross apply

    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b



方法2:



select 

    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))

from 

    (select distinct COl1 from Tab) a

cross apply

    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)

                .query('<Tab>

                {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}

                {concat("",string(/Tab[last()]/@COl2))}

                </Tab>')

                )b



SQL05用CTE:



;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)

,Roy2 as

(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 

union all 

select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)

select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)





生成结果:

/*

Col1        COl2

----------- ------------

1           a,b,c

2           d,e

3           f



(3 行受影响)

*/





拆分表:



--> --> (Roy)生成測試數據

 

if not object_id('Tab') is null

    drop table Tab

Go

Create table Tab([Col1] int,[COl2] nvarchar(5))

Insert Tab

select 1,N'a,b,c' union all

select 2,N'd,e' union all

select 3,N'f'

Go



SQL2000用辅助表:

if object_id('Tempdb..#Num') is not null

    drop table #Num

go

select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b

Select 

    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 

from 

    Tab a,#Num b

where

    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','





SQL2005用Xml:



select 

    a.COl1,b.Col2

from 

    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a

outer apply

    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b









SQL05用CTE:



;with roy as 

(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab

union all

select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''

)

select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)



生成结果:

/*

Col1        COl2

----------- -----

1           a

1           b

1           c

2           d

2           e

3           f

*/

dpzc_love 2009-05-13

打赏
举报

自己先顶一下

字符串拆分问题

相关推荐