问题描述
我有一个这样的 SQL 表:
I have a SQL Table like this:
| SomeID | OtherID | Data
+----------------+-------------+-------------------
| abcdef-..... | cdef123-... | 18,20,22
| abcdef-..... | 4554a24-... | 17,19
| 987654-..... | 12324a2-... | 13,19,20
是否有一个查询,我可以在其中执行类似 SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......'
的查询,该查询返回单个行,如下所示:
is there a query where I can perform a query like SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......'
that returns individual rows, like this:
| OtherID | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19
基本上将逗号处的数据拆分为单独的行?
Basically split my data at the comma into individual rows?
我知道将 逗号分隔
字符串存储到关系数据库中听起来很愚蠢,但在消费者应用程序中的正常用例确实很有帮助.
I am aware that storing a comma-separated
string into a relational database sounds dumb, but the normal use case in the consumer application makes that really helpful.
我不想在应用程序中进行拆分,因为我需要分页,所以我想在重构整个应用程序之前探索选项.
I don't want to do the split in the application as I need paging, so I wanted to explore options before refactoring the whole app.
它是 SQL Server 2008
(非 R2).
推荐答案
你可以使用 SQL Server 的精彩递归函数:
You can use the wonderful recursive functions from SQL Server:
示例表:
CREATE TABLE Testdata
(
SomeID INT,
OtherID INT,
String VARCHAR(MAX)
);
INSERT Testdata SELECT 1, 9, '18,20,22';
INSERT Testdata SELECT 2, 8, '17,19';
INSERT Testdata SELECT 3, 7, '13,19,20';
INSERT Testdata SELECT 4, 6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';
查询
WITH tmp(SomeID, OtherID, DataItem, String) AS
(
SELECT
SomeID,
OtherID,
LEFT(String, CHARINDEX(',', String + ',') - 1),
STUFF(String, 1, CHARINDEX(',', String + ','), '')
FROM Testdata
UNION all
SELECT
SomeID,
OtherID,
LEFT(String, CHARINDEX(',', String + ',') - 1),
STUFF(String, 1, CHARINDEX(',', String + ','), '')
FROM tmp
WHERE
String > ''
)
SELECT
SomeID,
OtherID,
DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option
输出
SomeID | OtherID | DataItem
--------+---------+----------
1 | 9 | 18
1 | 9 | 20
1 | 9 | 22
2 | 8 | 17
2 | 8 | 19
3 | 7 | 13
3 | 7 | 19
3 | 7 | 20
4 | 6 |
9 | 11 | 1
9 | 11 | 2
9 | 11 | 3
9 | 11 | 4
这篇关于将逗号分隔的字符串转换为单独的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!