使用c#函数加速查询交叉应用

DECLARE @tblA TABLE(ID INT,YourString VARCHAR(MAX));
INSERT INTO @tblA VALUES
 (123,'1=A&3=B&8=B&11=A&12=R&17=BX&19=Z&20=B&21=AK&22=AH&24=A&27=A&28=A&29=C&31=A&32=S&33=N&34=H&35=Z&37=A&38=Z&39=A&41=D&42=G&49=A&52=Z&53=C&54=D&55=Z&56=Z&57=Z&58=A&61=B&63=B&65=A&66=A&67=A&68=A&69=A&71=G&73=Q&74=AB&76=B&77=Z&79=F&80=B&82=D&83=B&89=BA&91=Z&92=Z&93=Z&95=R&97=B&98=Z&99=G&100=G&101=B&106=C&109=Z&110=A&111=Z&112=C&116=B&118=Z&120=B&121=A&122=A&124=E&125=Z&128=A&131=Z&137=F&141=A&142=B&143=Z&144=B&146=C&148=Z&153=A&157=A&164=A&167=Z&168=Z&170=C&188=E&200=A&203=B&227=Z&235=C&237=B&238=G&258=Z&259=Z&268=Z&272=B&274=D&284=B&285=C&288=A&289=Z&290=A&299=M&300=D&313=Z&315=Z&322=A&324=Z&327=Z&358=E&360=B&365=A&366=A&375=B&376=B&379=Z&381=Z&383=M&387=Z&390=Z&391=Z&392=B&393=Z&398=MA&400=Z&403=B&404=A&407=B&408=BT&410=B&412=A&413=F&414=B&415=F&416=B&419=Z&421=Z&430=C&433=Z&435=C&440=Z&441=PG&448=A&453=A&458=B&459=Z&460=Z&463=A&466=Z&468=Z&472=Z&474=A&475=Z&492=Z&496=Z&498=A&504=A&506=B&507=Z&514=D&518=B&519=Z&523=C&536=A&554=Z&557=Z&560=A&582=Z&584=A&586=Z&587=Z&588=B&591=C&599=A&603=Z&610=Z&614=B&625=A&664=A&666=C&668=C&670=Z&672=L&674=L&698=B&699=A&720=BB&745=Z&754=A&756=Z&759=Z&762=Z&766=AA&767=AA&769=A&772=HB&785=B&787=Z&788=Z&789=Z&790=Z&791=Z&792=Z&794=Z&795=A&796=Z&803=A&808=Z&809=DC&812=G&813=G&815=G&821=K&822=M&824=ES&828=Z&829=GA&831=SN&837=BC&841=K&845=K&851=S&852=Z&853=B&854=WU&855=B&856=WU&857=A&859=A&861=B&863=C&865=A&867=B&869=Z&873=ZA&882=CM&889=CD&921=Z&922=Z&924=Z&925=Z&932=B&933=Z&941=Z&952=RJ&954=Z&967=P&984=A&985=H&1002=D&1007=F&1041=F&1052=BR&1053=BR&1056=PD&1057=NA&1095=H&1104=B&1121=A&1122=M&1123=D&1162=G&1163=A&1164=B&1165=A&1166=E&1174=A&1182=A&1183=A&1185=C&1188=Z&1202=KG&1211=A&1223=B&1229=Z&1231=A&1233=Z&1237=L&1256=TW&1258=C&1259=BH&1260=D&1261=A&1272=C&1296=Z&1298=Z&1299=A&1302=A&1303=A&1308=C&1309=J&1313=A&1316=B&1317=Z&1318=A&1319=Z&1323=D&1327=A&1328=A&1329=Z&1330=Z&1331=A&1332=Z&1333=Z&1335=DS&1339=Z&1345=AE&1346=AG&1352=Z&1356=Z&1366=Z&1367=B&1368=B&1369=A&1370=W&1371=A&1373=Z&1379=GQ&1381=A&1387=CX&1388=Z&1389=EU&1391=E&1392=J&1397=Z&1399=Z&1401=Z&1405=A&1406=CN&1412=Z&1416=Z&1417=B&1419=Z&1421=Z&1424=Z&1428=Z&1431=B&1432=B&1436=A&1437=Z&1441=CB&1442=A&1443=Z&1449=A&1452=Z&1453=Z&1470=A&1473=Z&1474=A&1475=Z&1477=D&1478=B&1479=B&1481=A&1488=A&1490=D&1493=E&1494=Z&1502=Y&1503=BH&1504=C&1505=B&1506=Z&1511=Z&1512=A&1513=KU&1518=A&1524=Z&1541=A&1549=A&1554=A&1623=A&1629=Z&1637=B&1640=Z&1642=Z&1643=Z&1644=B&1661=A&1673=K&1681=A&1771=A&1772=Z&1787=Z&1788=Z&1790=Z&1793=Z&1837=A&1838=Z&1839=Z&1854=Z&1855=Z&1856=Z&1858=Z&1867=B&1872=B&1873=B&1879=Z&1881=B&1902=Z&1910=Z&1916=B&1917=A&1918=B&1920=A&1927=A&1928=Z&1963=Z&1964=Z&1968=A&1974=A&1975=Z&1976=Z&1993=Z&2007=A&2010=Z&2011=A&2012=Z&2015=Z&2022=Z&2023=Z&2024=Z&2032=Z&2037=Z&MPC-9002=AA&MPC-9006=AG&'),(165,'1=A&2=R&4=B&33=G&34=Q&46=Y&49=A&75=AH&589=C&590=A&803=IG&812=RB&813=RB&814=RB&815=RB&841=V&843=V&844=V&845=V&851=S&853=B&854=KJ&855=B&856=KJ&857=B&858=KJ&859=B&860=KJ&861=B&862=RB&863=B&864=RB&865=B&866=RB&867=B&881=AC&883=GE&889=M&897=IS&922=C&965=CP&1323=C&');

– 您可以通过将数据转换为JSON数组来轻松拆分数据
– 像1 = A& 3 = B& 8 = B& 11 = A& 12 = R的值将是[“1 = A”,“3 = B”,“8 = B”,“11 = A”,“12 = R”]

SELECT ID,A.[key] AS OrdPosition,A.[value] AS Fragment
FROM @tblA 
CROSS APPLY OPENJSON('["' + REPLACE(YourString,'&','","') + '"]') A

– 你的第二张桌子是双折的.我们也可以在一个查询中拆分它

DECLARE @tblB TABLE(ID INT,YourString VARCHAR(MAX));
INSERT INTO @tblB VALUES
 (1,'1=A&1323=D!1=B&1323=D!1=A&1323=E!1=F&1323=D!1=B&1323=E!1=F&1323=E'),(2,'1=B&142=D&1323=D&1470=B'),(3,'1=A&1323=E&1470=C');

SELECT ID,A.[key] AS OrdPositionA,B.[key] AS OrdPositionB,B.[value] AS Fragment
FROM @tblB
CROSS APPLY OPENJSON('["' + REPLACE(YourString,'!',"') + '"]') A
CROSS APPLY OPENJSON('["' + REPLACE(A.[value],"') + '"]') B

将这些方法与简单连接结合起来应该很容易.

性能

OPENJSON表现相当不错.在this answer中,我比较了STRING_SPLIT,OPENJSON和众所周知的delimited8k()函数.值得一试……

但这是你真正应该做的：

使用上面的查询将数据存储在片段中.无论什么时候需要它,你都可以使用STRING_AGG()来获取长字符串,但繁重的工作就是分裂.一次又一次地做到这一点并不是很聪明……

更新：更好：

用于@tlbA

SELECT ID,A.[value] AS Fragment,x.value('/x[1]','varchar(10)') AS FragmentNumber,x.value('/x[2]','varchar(10)') AS FragmentCharacter
FROM @tblA 
CROSS APPLY OPENJSON('["' + REPLACE(YourString,"') + '"]') A
CROSS APPLY(SELECT CAST('<x>' + REPLACE(A.[value],'=','</x><x>') + '</x>' AS XML)) B(x)
ORDER BY ID,OrdPosition

这对@tblB来说

SELECT ID,B.[value] AS Fragment,'varchar(10)') AS FragmentCharacter
FROM @tblB
CROSS APPLY OPENJSON('["' + REPLACE(YourString,"') + '"]') B
CROSS APPLY(SELECT CAST('<x>' + REPLACE(B.[value],'</x><x>') + '</x>' AS XML)) C(x)
ORDER BY ID,OrdPositionA,OrdPositionB

这将返回整齐分离的每个值.将其填入物理表,放置索引并享受您获得的速度.

将值存储在此类表中,并使用简单的STRING_AGG()查询获取原始的大字符串…

更新2

回答你的评论,这是加入这些结果的简单方法：

DECLARE @tblA TABLE(ID INT,'1=A&2=R&4=B&33=G&34=Q&46=Y&49=A&75=AH&589=C&590=A&803=IG&812=RB&813=RB&814=RB&815=RB&841=V&843=V&844=V&845=V&851=S&853=B&854=KJ&855=B&856=KJ&857=B&858=KJ&859=B&860=KJ&861=B&862=RB&863=B&864=RB&865=B&866=RB&867=B&881=AC&883=GE&889=M&897=IS&922=C&965=CP&1323=C&');

DECLARE @tblB TABLE(ID INT,'1=A&1323=E&1470=C');

WITH tblA AS
(
    SELECT ID,'varchar(10)') AS FragmentLeft,'varchar(10)') AS FragmentRight
    FROM @tblA 
    CROSS APPLY OPENJSON('["' + REPLACE(YourString,"') + '"]') A
    CROSS APPLY(SELECT CAST('<x>' + REPLACE(A.[value],'</x><x>') + '</x>' AS XML)) B(x)
),tblB AS
(
    SELECT ID,'varchar(10)') AS FragmentRight
    FROM @tblB
    CROSS APPLY OPENJSON('["' + REPLACE(YourString,"') + '"]') A
    CROSS APPLY OPENJSON('["' + REPLACE(A.[value],"') + '"]') B
    CROSS APPLY(SELECT CAST('<x>' + REPLACE(B.[value],'</x><x>') + '</x>' AS XML)) C(x)
)
SELECT a.Fragment,a.ID AS ID_a,a.OrdPosition AS OrdPosition_a,b.ID AS ID_b,b.OrdPositionA AS OrdPosition_b1,b.OrdPositionB AS OrdPosition_b2
FROM tblA a
INNER JOIN tblB b ON a.FragmentLeft=b.FragmentLeft AND a.FragmentRight=b.FragmentRight;

结果

+----------+------+---------------+------+----------------+----------------+
| Fragment | ID_a | OrdPosition_a | ID_b | OrdPosition_b1 | OrdPosition_b2 |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 123  | 0             | 1    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 165  | 0             | 1    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 123  | 0             | 1    | 2              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 165  | 0             | 1    | 2              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 123  | 0             | 3    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 165  | 0             | 3    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 1    | 3              | 1              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 1    | 1              | 1              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 1    | 0              | 1              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 2    | 0              | 2              |
+----------+------+---------------+------+----------------+----------------+

像这样读：

>片段1 = A在a行123和165a中是常见的,其中第一个或第三个块中的b行1或3(从零开始的索引)在第一个位置
>片段1323 = D在a行123中与b行1和2相同.它是a行中的第297个片段和……(依此类推……)

如果这对您没有帮助：请提供预期的输出以及您的加入规则.