sqlserver 如何将多列字符串 分割成多行数据。

ESinker 项目经理  2017-11-02 02:30:11
问题比较难以描述。直接上图,有如下数据表



需求就是按照特定的字符串分割并对应 变成如下数据



尽量不要使用存储函数等。因为没有权限。
...全文
1245 8 点赞 打赏 收藏 举报
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
道素 2017-11-03
你是说不要其他辅助表,纯粹用SQL?

WITH testdata(id,value,[name]) AS (
   SELECT 1,'23|24|25|26|29',N'啊,我,额,医,五' UNION ALL
   SELECT 1,'33|aa|bb|cc|ss',N'波,破,摸,佛,各' UNION ALL
   SELECT 2,'35|BB|CC|YY|RR',N'诶,必,塞,地,伊' UNION ALL
   SELECT 2,'56',N'我六' UNION ALL 
   SELECT 3,'34|ab',N'三四,不知道咧' UNION ALL
   SELECT 3,'58',N'舞吧'
)
SELECT id,t.value,t.name,v,n FROM testdata AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(t.value,'|','</n><n>')+'</n>'),(CONVERT(XML,'<n>'+REPLACE(t.[name],',','</n><n>')+'</n>')))) c(x1,x2)
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','varchar(100)') AS v FROM c.x1.nodes('n')x(n)) vs
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','nvarchar(100)') AS n FROM c.x2.nodes('n')x(n)) ns
WHERE vs.rn=ns.rn

+----+----------------+-----------+----+------+
| id | value          | name      | v  | n    |
+----+----------------+-----------+----+------+
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 23 | 啊    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 24 | 我    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 25 | 额    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 26 | 医    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 29 | 五    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | 35 | 诶    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | BB | 必    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | CC | 塞    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | YY | 地    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | RR | 伊    |
| 2  | 56             | 我六        | 56 | 我六   |
| 3  | 34|ab          | 三四,不知道咧   | 34 | 三四   |
| 3  | 34|ab          | 三四,不知道咧   | ab | 不知道咧 |
| 3  | 58             | 舞吧        | 58 | 舞吧   |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | 33 | 波    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | aa | 破    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | bb | 摸    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | cc | 佛    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | ss | 各    |
+----+----------------+-----------+----+------+
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-03
其它的种种方式, 都不适合你, SQL写得再好, 也不如现成的数据摆在那里。 设计很重要, 没弄好就是一步错步步错。
  • 打赏
  • 举报
回复
ESinker 2017-11-03
引用 4 楼 ch21st 的回复:
你是说不要其他辅助表,纯粹用SQL?

WITH testdata(id,value,[name]) AS (
   SELECT 1,'23|24|25|26|29',N'啊,我,额,医,五' UNION ALL
   SELECT 1,'33|aa|bb|cc|ss',N'波,破,摸,佛,各' UNION ALL
   SELECT 2,'35|BB|CC|YY|RR',N'诶,必,塞,地,伊' UNION ALL
   SELECT 2,'56',N'我六' UNION ALL 
   SELECT 3,'34|ab',N'三四,不知道咧' UNION ALL
   SELECT 3,'58',N'舞吧'
)
SELECT id,t.value,t.name,v,n FROM testdata AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(t.value,'|','</n><n>')+'</n>'),(CONVERT(XML,'<n>'+REPLACE(t.[name],',','</n><n>')+'</n>')))) c(x1,x2)
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','varchar(100)') AS v FROM c.x1.nodes('n')x(n)) vs
OUTER APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn,x.n.value('.','nvarchar(100)') AS n FROM c.x2.nodes('n')x(n)) ns
WHERE vs.rn=ns.rn

+----+----------------+-----------+----+------+
| id | value          | name      | v  | n    |
+----+----------------+-----------+----+------+
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 23 | 啊    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 24 | 我    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 25 | 额    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 26 | 医    |
| 1  | 23|24|25|26|29 | 啊,我,额,医,五 | 29 | 五    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | 35 | 诶    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | BB | 必    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | CC | 塞    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | YY | 地    |
| 2  | 35|BB|CC|YY|RR | 诶,必,塞,地,伊 | RR | 伊    |
| 2  | 56             | 我六        | 56 | 我六   |
| 3  | 34|ab          | 三四,不知道咧   | 34 | 三四   |
| 3  | 34|ab          | 三四,不知道咧   | ab | 不知道咧 |
| 3  | 58             | 舞吧        | 58 | 舞吧   |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | 33 | 波    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | aa | 破    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | bb | 摸    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | cc | 佛    |
| 1  | 33|aa|bb|cc|ss | 波,破,摸,佛,各 | ss | 各    |
+----+----------------+-----------+----+------+
这个方法 也想过了,但是现在就是不敢确定的就是解析xml时的顺序是怎么样的。因为查了官方文档找不到有关的说明,所以没有敢擅自使用
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-03
这个东西从最开始就错了。 如果你追求性能, 一开始就不应该设计成这样。按数据库规范来说也不能这样设计。 当然, 也不是一无是处, 没性能要求的情况下, 这样做也无可厚非,只不过不适合你目前的情况了。 增加2个表就可以了。
IF OBJECT_ID('value_mid') IS NOT NULL DROP TABLE value_mid
CREATE TABLE value_mid (
	mid INT IDENTITY(1,1),
	id INT NOT NULL,
	[value] NVARCHAR(20) NOT NULL,
	CONSTRAINT PK_value_mid PRIMARY KEY NONCLUSTERED(
		mid
	)
)
CREATE CLUSTERED INDEX IX_value_mid_id_vlue ON value_mid(id,[value])

IF OBJECT_ID('name_mid') IS NOT NULL DROP TABLE name_mid
CREATE TABLE name_mid (
	mid INT IDENTITY(1,1),
	id INT NOT NULL,
	[name] NVARCHAR(20) NOT NULL,
	CONSTRAINT PK_name_mid PRIMARY KEY NONCLUSTERED(
		mid
	)
)
CREATE CLUSTERED INDEX IX_name_mid_id_vlue ON name_mid(id,[name])

增加完之后, 把原表中的值按切分函数切分后插入到这两个表。 此外, 以后插入原表时, 也要往这2个表里插入一份。 这样就清净了, 以你想查啥, 直接查这两个表就是。
  • 打赏
  • 举报
回复
ESinker 2017-11-03
引用 3 楼 RINK_1 的回复:
你前两天不是问过类似的问题了吗,那些方法不行?你说的不能用存储函数,指的是存储过程还是一些系统表,比如spt_values。如果是这个表,那你自己去构造一个序号辅助表,然后用这个表去进行处理。


WITH TABLE_SEQ
AS
(SELECT 0 AS NUMBER
 UNION ALL
 SELECT NUMBER+1 FROM TABLE_SEQ
 WHERE NUMBER+1<=2000),

CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS SEQ_1
 FROM TABLE)
 

SELECT A.ID,A.single_VALUE AS VALUE_N,B.single_NAME AS NAME_N
FROM
(SELECT A.ID,SEQ_1,
       SUBSTRING(VALUE_N,B.NUMBER,CHARINDEX('|',VALUE_N+'|',B.NUMBER)-B.NUMBER) AS single_VALUE,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX('|','|'+A.VALUE_N,B.number)=B.number ) AS A 
JOIN
(SELECT A.ID,SEQ_1,
       SUBSTRING(NAME_N,B.NUMBER,CHARINDEX(',',NAME_N+',',B.NUMBER)-B.NUMBER) AS single_NAME,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX(',',','+A.NAME_N,B.number)=B.number ) AS B
ON A.ID=B.ID AND A.SEQ_1=B.SEQ_1 AND A.SEQ_2=B.SEQ_2  
ORDER BY A.ID,A.SEQ_1,B.SEQ_2
OPTION(MAXRECURSION 0)

这个sql 已经写出来了。但是由于数据量问题。性能很差。希望看看更多的解决方案
  • 打赏
  • 举报
回复
RINK_1 2017-11-02
你前两天不是问过类似的问题了吗,那些方法不行?你说的不能用存储函数,指的是存储过程还是一些系统表,比如spt_values。如果是这个表,那你自己去构造一个序号辅助表,然后用这个表去进行处理。


WITH TABLE_SEQ
AS
(SELECT 0 AS NUMBER
 UNION ALL
 SELECT NUMBER+1 FROM TABLE_SEQ
 WHERE NUMBER+1<=2000),

CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS SEQ_1
 FROM TABLE)
 

SELECT A.ID,A.single_VALUE AS VALUE_N,B.single_NAME AS NAME_N
FROM
(SELECT A.ID,SEQ_1,
       SUBSTRING(VALUE_N,B.NUMBER,CHARINDEX('|',VALUE_N+'|',B.NUMBER)-B.NUMBER) AS single_VALUE,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX('|','|'+A.VALUE_N,B.number)=B.number ) AS A 
JOIN
(SELECT A.ID,SEQ_1,
       SUBSTRING(NAME_N,B.NUMBER,CHARINDEX(',',NAME_N+',',B.NUMBER)-B.NUMBER) AS single_NAME,
       ROW_NUMBER() OVER (PARTITION BY ID,SEQ_1 ORDER BY NUMBER) AS SEQ_2
FROM CTE A 
JOIN TABLE_SEQ AS B ON CHARINDEX(',',','+A.NAME_N,B.number)=B.number ) AS B
ON A.ID=B.ID AND A.SEQ_1=B.SEQ_1 AND A.SEQ_2=B.SEQ_2  
ORDER BY A.ID,A.SEQ_1,B.SEQ_2
OPTION(MAXRECURSION 0)

  • 打赏
  • 举报
回复
听雨停了 2017-11-02

CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
  @str VARCHAR(MAX) ,
  @split VARCHAR(10)
)
RETURNS TABLE
    AS 
RETURN
    ( SELECT    B.id
      FROM      ( SELECT    [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
                            + '</v>')
                ) A
      OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)')
                    FROM    A.[value].nodes('/v') N ( v )
                  ) B
    )
执行上面的代码得先创建这个函数
  • 打赏
  • 举报
回复
听雨停了 2017-11-02

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id INT,
VALUE VARCHAR(20),
NAME VARCHAR(20)
)
INSERT INTO #tab
SELECT 1,'12|23','你好,hello' union all
SELECT 1,'456|1|35','Abc,efc,kkk' UNION ALL
SELECT 2,'12','pppp'
--测试数据结束

IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
SELECT a.id AS id_mstr,a.[VALUE],a.name,b.*,identity (int,1,1) as rn into #t FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.[VALUE],'|') b
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
SELECT a.id AS id_mstr,a.[VALUE],a.name,b.*,identity (int,1,1) as rn into #t2 FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.name,',') b

SELECT a.id_mstr,
a.value,
a.name,
a.id AS value_n,
b.id AS name_n
FROM #t a
INNER JOIN #t2 b
ON a.rn = b.rn
AND a.id_mstr = b.id_mstr
AND a.value = b.value

  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2017-11-02 02:30
社区公告
暂无公告