- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
如何在 SQL Server 中将包含矩阵的字符串拆分为表?字符串具有列分隔符和行分隔符。
假设我有一个字符串:
declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';
预期结果(在三个单独的列中):
+---+---+---+
| A | B | C |
+---+---+---+
| D | E | F |
+---+---+---+
| X | Y | Z |
+---+---+---+
我正在寻找未定义列数和行数的通用解决方案。所以字符串:
declare @str varchar(max)='A,B;D,E';
将被分成两列的表格:
+---+---+
| A | B |
+---+---+
| D | E |
+---+---+
我的努力。我的第一个想法是使用动态 SQL 将字符串转换为:插入 dbo.temp 值 (...)
这种方法虽然速度非常快,但有一个小缺点,因为它需要首先创建一个具有正确列数的表。我在the answer to my own question below中介绍了这个方法只是为了保持问题简短。
另一个想法是将字符串写入服务器上的 CSV 文件,然后从中批量插入
。虽然我不知道该怎么做,也不知道第一个和第二个想法的表现如何。
我之所以问这个问题是因为我想将数据从Excel导入到SQL Server。由于我尝试过不同的 ADO 方法,这种发送矩阵字符串的方法取得了压倒性的胜利,尤其是当字符串长度增加时。我在这里问了一个双胞胎弟弟的问题:Turn Excel range into VBA string 您可以在其中找到如何从 Excel 范围准备此类字符串的建议。
赏金 我决定奖励马特。我高度重视肖恩·兰格的回答。谢谢肖恩。我喜欢马特的回答,因为它简单而简短。除了马特和肖恩之外的不同方法可以并行使用,所以暂时我不接受任何答案(更新:最后,几个月后,我接受了马特的答案)。我要感谢艾哈迈德·赛义德 (Ahmed Saeed) 的 VALUES 想法,因为它是我开始时的答案的一个很好的演变。当然,它比不上马特或肖恩的。我对每个答案都投了赞成票。我将感谢您对使用这些方法的任何反馈。谢谢你的探索。
最佳答案
好吧,这个谜题引起了我的兴趣,所以我决定看看是否可以在没有任何循环的情况下做到这一点。要使其发挥作用,有几个先决条件。首先,我们假设您有某种统计表。如果你没有,这是我的代码。我将其保留在我使用的每个系统上。
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
这个难题的第二部分是需要一个基于集合的字符串分割器。我最喜欢的是超快的 Jeff Moden 分路器。需要注意的是,它仅适用于 8,000 以下的 varchar 值。这对于我使用的大多数分隔字符串来说已经足够了。您可以在这里找到 Jeff Moden 的分离器 (DelimitedSplit8K)。
http://www.sqlservercentral.com/articles/Tally+Table/72993/
最后但并非最不重要的是,我在这里使用的技术是动态交叉表。这是我从杰夫·莫登那里学到的另一件事。他在这里发表了一篇关于该主题的精彩文章。
http://www.sqlservercentral.com/articles/Crosstab/65048/
将所有这些放在一起,您可以想出这样的东西,它会非常快并且可以很好地扩展。
declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';
declare @StaticPortion nvarchar(2000) =
'declare @str varchar(max)=''' + @str + ''';with OrderedResults as
(
select s.ItemNumber
, s.Item as DelimitedValues
, x.ItemNumber as RowNum
, x.Item
from dbo.DelimitedSplit8K(@str, '';'') s
cross apply dbo.DelimitedSplit8K(s.Item, '','') x
)
select '
declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults group by ItemNumber';
select @DynamicPortion = @DynamicPortion +
', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then Item end) as Column' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= (select MAX(len(Item) - LEN(replace(Item, ',', ''))) + 1
from dbo.DelimitedSplit8K(@str, ';')
)
declare @SqlToExecute nvarchar(max) = @StaticPortion + stuff(@DynamicPortion, 1, 1, '') + @FinalStaticPortion
exec sp_executesql @SqlToExecute
--编辑--
这里是 DelimitedSplit8K 函数,以防链接无效。
ALTER FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
关于sql - 在 SQL Server 中将字符串拆分为给定行分隔符和列分隔符的表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39752188/
猫f1.txt阿曼维沙尔阿杰贾伊维杰拉胡尔曼尼什肖比特批评塔夫林现在输出应该符合上面给定的条件 最佳答案 您可以在文件读取循环中设置一个计数器并打印它, 计数=0 读取行时做 让我们数一数++ if
我正在尝试查找文件 1 和文件 2 中的共同行。如果公共(public)行存在,我想写入文件 2 中的行,否则打印文件 1 中的非公共(public)行。fin1 和 fin2 是这里的文件句柄。它读
我有这个 SQL 脚本: CREATE TABLE `table_1` ( `IDTable_1` int(11) NOT NULL, PRIMARY KEY (`IDTable_1`) );
我有 512 行要插入到数据库中。我想知道提交多个插入内容是否比提交一个大插入内容有任何优势。例如 1x 512 行插入 -- INSERT INTO mydb.mytable (id, phonen
如何从用户中选择user_id,SUB(row, row - 1),其中user_id=@userid我的表用户,id 为 1、3、4、10、11、23...(不是++) --id---------u
我曾尝试四处寻找解决此问题的最佳方法,但我找不到此类问题的任何先前示例。 我正在构建一个基于超本地化的互联网购物中心,该区域分为大约 3000 个区域。每个区域包含大约 300 个项目。它们是相似的项
preg_match('|phpVersion = (.*)\n|',$wampConfFileContents,$result); $phpVersion = str_replace('"','',
我正在尝试创建一个正则表达式,使用“搜索并替换全部”删除 200 个 txt 文件的第一行和最后 10 行 我尝试 (\s*^(\h*\S.*)){10} 删除包含的前 10 行空白,但效果不佳。 最
下面的代码从数据库中获取我需要的信息,但没有打印出所有信息。首先,我知道它从表中获取了所有正确的信息,因为我已经在 sql Developer 中尝试过查询。 public static void m
很难说出这里问的是什么。这个问题是含糊的、模糊的、不完整的、过于宽泛的或修辞性的,无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开它,visit the help center 。 已关
我试图在两个表中插入记录,但出现异常。您能帮我解决这个问题吗? 首先我尝试了下面的代码。 await _testRepository.InsertAsync(test); await _xyzRepo
这个基本的 bootstrap CSS 显示 1 行 4 列: Text Text Text
如果我想从表中检索前 10 行,我将使用以下代码: SELECT * FROM Persons LIMIT 10 我想知道的是如何检索前 10 个结果之后的 10 个结果。 如果我在下面执行这段代码,
今天我开始使用 JexcelApi 并遇到了这个:当您尝试从特定位置获取元素时,不是像您通常期望的那样使用sheet.getCell(row,col),而是使用sheet.getCell(col,ro
我正在尝试在我的网站上开发一个用户个人资料系统,其中包含用户之前发布的 3 个帖子。我可以让它选择前 3 条记录,但它只会显示其中一条。我是不是因为凌晨 2 点就想编码而变得愚蠢? query($q)
我在互联网上寻找答案,但找不到任何答案。 (我可能问错了?)我有一个看起来像这样的表: 我一直在使用查询: SELECT title, date, SUM(money) FROM payments W
我有以下查询,我想从数据库中获取 100 个项目,但 host_id 多次出现在 urls 表中,我想每个 host_id 从该表中最多获取 10 个唯一行。 select * from urls j
我的数据库表中有超过 500 行具有特定日期。 查询特定日期的行。 select * from msgtable where cdate='18/07/2012' 这将返回 500 行。 如何逐行查询
我想使用 sed 从某一行开始打印 n 行、跳过 n 行、打印 n 行等,直到文本文件结束。例如在第 4 行声明,打印 5-9,跳过 10-14,打印 15-19 等 来自文件 1 2 3 4 5 6
我目前正在执行验证过程来检查用户的旧密码,但问题是我无法理解为什么我的查询返回零行,而预期它有 1 行。另一件事是,即使我不将密码文本转换为 md5,哈希密码仍然得到正确的答案,但我不知道为什么会发生
我是一名优秀的程序员,十分优秀!