字段中字符串切割问题

2017-01-01 04:07:11 746 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

lixizheng 墨衍会员 · AI 创作全网分发关注 + 加入社区

使用VNC可视化Docker容器 Docker容器可视化阅读详情

数据库结构如下：

id part
1 225100:02:3:20160725;225100:01:20:20160601
2 225100:03:1:20160701
3 225200:02:12:20160501
4 :01:3:20160725
5 225200:01:2:20160401
6 225200:03::20160411
7 225300:02:3:20160725
8 225300:02:14:
9 225300:01:3:20160201
10 225300:01:3:20160301

-------------------------------------------------
请问该如何写sql，才能达到这样的效果：

id part1 part2
1 225100:02:3:20160725 225100:01:20:20160601
2 225100:03:1:20160701
3 225200:02:12:20160501
4 :01:3:20160725
5 225200:01:2:20160401
6 225200:03::20160411
7 225300:02:3:20160725
8 225300:02:14:
9 225300:01:3:20160201
10 225300:01:3:20160301

或者如果能达到这样的效果更好：
id a b c d
1 225100 02 3 20160725
1 225100 01 20 20160601
2 225100 03 1 20160701
3 225200 02 12 20160501
4 01 3 20160725
5 225200 01 2 20160401
6 225200 03 20160411
7 225300 02 3 20160725
8 225300 02 14
9 225300 01 3 20160201
10 225300 01 3 20160301

是SQLLite数据库，有些方法或功能可能实现不了，请大神帮帮忙，实在不行，就只实现part1 part2这样的效果也行。

...全文

UE5在Ubuntu22.04.4下的高效开发环境配置指南本文详细介绍了在Ubuntu 22.04.4系统上配置UE5高效开发环境的完整指南，包括系统准备、显卡驱动安装、UE5引擎编译与配置、开发工具链设置以及性能优化技巧。通过逐步指导，帮助开发者快速搭建稳定的UE5开发环境，解决常见问题，提升开发效率。阅读详情

lixizheng

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

道素 2017-01-03

打赏
举报

引用 8 楼 lixizheng 的回复:

我开始没注意，您给的是可以正常运行的，太感谢了！！！

我就是用SqlLite测试的，否则不需要 WITH RECURSIVE cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100) 生成数字序列，SQL Server本身有master..spt_values 如果你不想这么写，自己建个表也可以，里面就保存1,2,3....这样的序列，其他的作用和上面的cnt 一样

道素 2017-01-02

打赏
举报

我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看


insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/

lixizheng 2017-01-02

打赏
举报

我开始没注意，您给的是可以正常运行的，太感谢了！！！

lixizheng 2017-01-02

打赏
举报

引用 5 楼 ch21st 的回复:

我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看


insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/

能不能给改动下，使之可以在sqllite下运行呢？

lixizheng 2017-01-02

打赏
举报

引用 5 楼 ch21st 的回复:

我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看


insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/

引用 5 楼 ch21st 的回复:

我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看


insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/

非常感谢，我放在sqlserver里运行是正常的，但这个使用的是sqllite数据库，而且只能使用sqllite这个数据库，您回复的两端代码在sqllite里都运行不了，有些功能sqllite上不支持。

lixizheng 2017-01-01

打赏
举报

引用 3 楼 ch21st 的回复:

你这不是已经解析出来了吗？至于分号分行你可以参考下面的做法


WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)

	select id, a.part, substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')-1) as part1
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)

/*
225100:02:3:20160725;225100:01:20:20160601	225100:02:3:20160725
225100:02:3:20160725;225100:01:20:20160601	225100:01:20:2016060
225100:03:1:20160701	225100:03:1:20160701
225200:02:12:20160501	225200:02:12:20160501
:01:3:20160725	:01:3:20160725
225200:01:2:20160401	225200:01:2:20160401
225200:03::20160411	225200:03::20160411
225300:02:3:20160725	225300:02:3:20160725
225300:02:14:	225300:02:14:
225300:01:3:20160201	225300:01:3:20160201
225300:01:3:20160301	225300:01:3:20160301
*/

这个别人给解答的，我运行后发现少了id为1的分号后面那条数据，我不知道怎么读取出来，能帮忙在那条SQL语句上改进一下吗？

道素 2017-01-01

打赏
举报

你这不是已经解析出来了吗？至于分号分行你可以参考下面的做法


WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)

	select id, a.part, substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')-1) as part1
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)

/*
225100:02:3:20160725;225100:01:20:20160601	225100:02:3:20160725
225100:02:3:20160725;225100:01:20:20160601	225100:01:20:2016060
225100:03:1:20160701	225100:03:1:20160701
225200:02:12:20160501	225200:02:12:20160501
:01:3:20160725	:01:3:20160725
225200:01:2:20160401	225200:01:2:20160401
225200:03::20160411	225200:03::20160411
225300:02:3:20160725	225300:02:3:20160725
225300:02:14:	225300:02:14:
225300:01:3:20160201	225300:01:3:20160201
225300:01:3:20160301	225300:01:3:20160301
*/

lixizheng 2017-01-01

打赏
举报

引用 1 楼 ch21st 的回复:



;with tb(id,part) AS (

  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all

  select 2,'225100:03:1:20160701' union all

  select 3,'225200:02:12:20160501' union all

  select 4,':01:3:20160725' union all

  select 5,'225200:01:2:20160401' union all

  select 6,'225200:03::20160411' union all

  select 7,'225300:02:3:20160725' union all

  select 8,'225300:02:14:' union all

  select 9,'225300:01:3:20160201' union all

  select 10,'225300:01:3:20160301'



),p AS (

select id

,r.si,DENSE_RANK()OVER(PARTITION BY id ORDER BY CONVERT(VARCHAR,r.si)) AS rn,'data'+LTRIM(ROW_NUMBER()OVER(PARTITION BY id,r.si ORDER BY d.k)) AS title

,d.v 

FROM tb

CROSS APPLY(values(CONVERT(XML,'<r><n>'+replace(replace(tb.part,':','</n><n>'),';','</n></r><r><n>')+'</n></r>'))) x(n)

OUTER APPLY (SELECT s.b.query('.') AS i,s.b.value('.','varchar(500)') si  FROM x.n.nodes('r')s(b)) r

OUTER APPLY (SELECT s.b.value('.','varchar(500)') AS v,ROW_NUMBER()OVER(ORDER BY GETDATE()) k FROM r.i.nodes('r/n')s(b)) d



)

--DROP TABLE #tt

SELECT * INTO #tt FROM p

SELECT * FROM #tt 

DECLARE @col VARCHAR(max),@sql VARCHAR(max)

SELECT @col=ISNULL(@col+',','')+title FROM #tt GROUP BY title ORDER BY title

PRINT @col

SET @sql='

SELECT id,'+@col+' FROM #tt

PIVOT(MAX(v) FOR title IN ('+@col+')) p'

EXEC(@sql)

/*

id	data1	data2	data3	data4

1	225100	01	20	20160601

1	225100	02	3	20160725

2	225100	03	1	20160701

3	225200	02	12	20160501

4		01	3	20160725

5	225200	01	2	20160401

6	225200	03		20160411

7	225300	02	3	20160725

8	225300	02	14	

9	225300	01	3	20160201

10	225300	01	3	20160301

*/

你好，这里使用的是SQLLite数据库，用不了存储过程，能有其它方法吗？目前做到这样了id1-10里的字符串全部转化为4个字段正常读取了，就是id为1的字符串，中间有个分号，等于里面有两条数据，不知道该怎么读取了。这是我目前的sql语句：
SELECT
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM chq3g4
-----------------

道素 2017-01-01

打赏
举报


;with tb(id,part) AS (
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

),p AS (
select id
,r.si,DENSE_RANK()OVER(PARTITION BY id ORDER BY CONVERT(VARCHAR,r.si)) AS rn,'data'+LTRIM(ROW_NUMBER()OVER(PARTITION BY id,r.si ORDER BY d.k)) AS title
,d.v 
FROM tb
CROSS APPLY(values(CONVERT(XML,'<r><n>'+replace(replace(tb.part,':','</n><n>'),';','</n></r><r><n>')+'</n></r>'))) x(n)
OUTER APPLY (SELECT s.b.query('.') AS i,s.b.value('.','varchar(500)') si  FROM x.n.nodes('r')s(b)) r
OUTER APPLY (SELECT s.b.value('.','varchar(500)') AS v,ROW_NUMBER()OVER(ORDER BY GETDATE()) k FROM r.i.nodes('r/n')s(b)) d

)
--DROP TABLE #tt
SELECT * INTO #tt FROM p
SELECT * FROM #tt 
DECLARE @col VARCHAR(max),@sql VARCHAR(max)
SELECT @col=ISNULL(@col+',','')+title FROM #tt GROUP BY title ORDER BY title
PRINT @col
SET @sql='
SELECT id,'+@col+' FROM #tt
PIVOT(MAX(v) FOR title IN ('+@col+')) p'
EXEC(@sql)
/*
id	data1	data2	data3	data4
1	225100	01	20	20160601
1	225100	02	3	20160725
2	225100	03	1	20160701
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/

字段中字符串切割问题

相关推荐