如何加入列中有多个值的表? [英] How to JOIN to a table that has multiple values in the column?
问题描述
我有一个包含错误代码字段的人员表，该字段可以包含多个错误代码(001、002、003...).我知道这是一个架构问题，但这是一个供应商应用程序，我无法控制架构，所以我必须使用我所拥有的.
I've got a person table that contains an error code field that can contain multiple error codes (001, 002, 003...). I know that's a schema problem but this is a vendor application and I have no control over the schema, so I have to work with what I've got.
还有一个包含 ErrorCode (char(3)) 和 Descript (char(1000)) 的 Error 表.在我的查询中，Person.ErrorCode 与 Error.ErrorCode 结合以获取相应描述的值.
There is also a Error table that contains ErrorCode (char(3)) and Descript (char(1000)). In my query the Person.ErrorCode is joined to the Error.ErrorCode to get the value of the corresponding description.
对于只有一个错误代码的人员记录，我可以毫无问题地获取相应的Descript.我想要做的是以某种方式连接存在多个错误的记录的 Descript 值.
For person records where there is only one error code, I can get the corresponding Descript with no problem. What I'm trying to do is somehow concat the Descript values for records where there are multiple errors.
例如，以下是错误表中的一些示例数据:
For example, here's some sample data from Error table:
ErrorCode Descript
001 Problem with person file
002 Problem with address file
003 Problem with grade
以下是我对 Person 的 SELECT 和 JOIN on Error 产生的列:
Here are the columns resulting from my SELECT on Person with a JOIN on Error:
Person.RecID Person.ErrorCode Error.Descript
12345 001 Problem with person file
12346 003 Problem with grade
12347 002,003
我想要的是:
Person.RecID Person.ErrorCode Error.Descript
12345 001 Problem with person file
12346 003 Problem with grade
12347 002,003 Problem with address file, Problem with grade
感谢建议！
推荐答案
您应该看到:"SQL Server 2005 及更高版本中的数组和列表，当表值参数不剪切时" by Erland Sommarskog，然后在 SQL Server 中有很多方法可以拆分字符串.本文涵盖了几乎所有方法的优点和缺点.通常，您需要创建一个拆分函数.这是如何使用拆分函数来连接行:
You should see: "Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog, then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method. in general, you need to create a split function. This is how a split function can be used to join rows:
SELECT
*
FROM dbo.yourSplitFunction(@Parameter) b
INNER JOIN YourCodesTable c ON b.ListValue=c.CodeValue
我更喜欢使用数字表方法在 TSQL 中拆分字符串 但在 SQL Server 中有多种拆分字符串的方法，请参阅上一个链接，其中解释了每种方法的优点和缺点.
I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.
要使 Numbers Table 方法起作用，您需要进行一次时间表设置，这将创建一个表 Numbers，其中包含从 1 到 10,000 的行:
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
设置 Numbers 表后，创建此拆分函数:
Once the Numbers table is set up, create this split function:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
您现在可以轻松地将 CSV 字符串拆分为一个表格并在其上加入:
You can now easily split a CSV string into a table and join on it:
DECLARE @ErrorCode table (ErrorCode varchar(20), Description varchar(30))
INSERT @ErrorCode VALUES ('001','Problem with person file')
INSERT @ErrorCode VALUES ('002','Problem with address file')
INSERT @ErrorCode VALUES ('003','Problem with grade')
DECLARE @Person table (RecID int, ErrorCode varchar(20))
INSERT @Person VALUES (12345 ,'001' )
INSERT @Person VALUES (12346 ,'003' )
INSERT @Person VALUES (12347 ,'002,003')
SELECT
p.RecID,c.ListValue,e.Description
FROM @Person p
CROSS APPLY dbo.FN_ListToTable(',',p.ErrorCode) c
INNER JOIN @ErrorCode e ON c.ListValue=e.ErrorCode
输出:
RecID ListValue Description
----------- ------------- -------------------------
12345 001 Problem with person file
12346 003 Problem with grade
12347 002 Problem with address file
12347 003 Problem with grade
(4 row(s) affected)
您可以使用 XML 技巧将行重新连接在一起:
you can use the XML trick to concatenate the rows back together:
SELECT
t1.RecID,t1.ErrorCode
,STUFF(
(SELECT
', ' + e.Description
FROM @Person p
CROSS APPLY dbo.FN_ListToTable(',',p.ErrorCode) c
INNER JOIN @ErrorCode e ON c.ListValue=e.ErrorCode
WHERE t1.RecID=p.RecID
ORDER BY p.ErrorCode
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
) AS ChildValues
FROM @Person t1
GROUP BY t1.RecID,t1.ErrorCode
输出:
RecID ErrorCode ChildValues
----------- -------------------- -----------------------------------------------
12345 001 Problem with person file
12346 003 Problem with grade
12347 002,003 Problem with address file, Problem with grade
(3 row(s) affected)
这会返回与上面相同的结果集，但可能会表现得更好:
This returns the same result set as above, but may perform better:
SELECT
t1.RecID,t1.ErrorCode
,STUFF(
(SELECT
', ' + e.Description
FROM (SELECT ListValue FROM dbo.FN_ListToTable(',',t1.ErrorCode)) c
INNER JOIN @ErrorCode e ON c.ListValue=e.ErrorCode
ORDER BY c.ListValue
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
) AS ChildValues
FROM @Person t1
GROUP BY t1.RecID,t1.ErrorCode
这篇关于如何加入列中有多个值的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
