SQL：在单个字段中具有多个值的嵌套 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5518612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Nested SELECT with multiple values in a single field
提问by Dexter
In my SQL 2005 DB, I have a table with values stored as IDs with relationships to other tables. So in my MyDBO.warrantytable, I'm storing product_id instead of product_name in order to save space. The product_name is stored in MyDBO.products.
在我的 SQL 2005 DB 中，我有一个表，其中的值存储为与其他表有关系的 ID。所以在我的MyDBO.warranty表中，我存储 product_id 而不是 product_name 以节省空间。product_name 存储在MyDBO.products 中。
When the marketing department pulls the demographic information, the query selects the corresponding name for each ID from related tables (trimmed down for brevity):
当营销部门拉取人口统计信息时，查询会从相关表中为每个 ID 选择对应的名称（为简洁起见进行了删减）：
SELECT w1.warranty_id AS "No.",
w1.created AS "Register Date"
w1.full_name AS "Name",
w1.purchase_date AS "Purchased",
(
SELECT p1.product_name
FROM WarrDBO.products p1 WITH(NOLOCK)
WHERE p1.product_id = i1.product_id
) AS "Product Purchased",
i1.accessories
FROM WarrDBO.warranty w1
LEFT OUTER JOIN WarrDBO.warranty_info i1
ON i1.warranty_id = w1.warranty_id
ORDER BY w1.warranty_id ASC
Now, my problem is that the "accessories" column on the warranty_info table stores several values:
现在，我的问题是warranty_info 表上的“accessories”列存储了几个值：
No. Register Date Name Purchased Accessories
---------------------------------------------------------------------
1500 1/1/2008 Smith, John Some Product 5,7,9
1501 1/1/2008 Hancock, John Another 2,3
1502 1/1/2008 Brown, James And Another 2,9
I need to do something similar with "Accessories" that I did with "Product" and pull accessory_namefrom the MyDBO.accessoriestable using accessory_id. I'm not sure where to start, because first I'd need to extract the IDs and then somehow concatenate multiple values into a string. So each line would have "accessoryname1,accessoryname2,accessoryname3":
我需要对“Accessories”做一些类似于我对“Product”所做的事情，并使用Accessories_id从MyDBO.accessories表中拉出Accessories_name。我不确定从哪里开始，因为首先我需要提取 ID，然后以某种方式将多个值连接成一个字符串。所以每一行都会有“accessoryname1,accessoryname2,accessoryname3”：
No. Register Date Name Purchased Accessories
---------------------------------------------------------------------
1500 1/1/2008 Smith, John Some Product Case,Bag,Padding
1501 1/1/2008 Hancock, John Another Wrap,Label
1502 1/1/2008 Brown, James And Another Wrap,Padding
How do I do this?
我该怎么做呢？
EDIT>> Posting my final code:
编辑>>发布我的最终代码：
I created this function:
我创建了这个函数：
CREATE FUNCTION SQL_GTOInc.Split
(
@delimited varchar(50),
@delimiter varchar(1)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
id INT identity(1,1), -- I use this column for numbering splitted parts
val INT
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(5)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
And updated my code accordingly:
并相应地更新了我的代码：
SELECT w1.warranty_id,
i1.accessories,
(
CASE
WHEN i1.accessories <> '' AND i1.accessories <> 'NULL' AND LEN(i1.accessories) > 0 THEN
STUFF(
(
SELECT ', ' + a1.accessory
FROM MyDBO.accessories a1
INNER JOIN MyDBO.Split(i1.accessories, ',') a2
ON a1.accessory_id = a2.val
FOR XML PATH('')
), 1, 1, ''
)
ELSE ''
END
) AS "Accessories"
FROM MyDBO.warranty w1
LEFT OUTER JOIN MyDBO.warranty_info i1
ON i1.warranty_id = w1.warranty_id
回答by Sean
You could write a table valued function that simply splits comma separated string into XML and turns XML nodes to rows.
您可以编写一个表值函数，该函数只是将逗号分隔的字符串拆分为 XML 并将 XML 节点转换为行。
See: http://www.kodyaz.com/articles//t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx
请参阅：http: //www.kodyaz.com/articles//t-sql-convert-split-delimed-string-as-rows-using-xml.aspx
Join to accessories through the result of function call, and stuff the result back to comma separated list of names.
通过函数调用的结果连接到附件，并将结果塞回到逗号分隔的名称列表中。
Untested code:
未经测试的代码：
SELECT w1.warranty_id AS "No.",
w1.created AS "Register Date"
w1.full_name AS "Name",
w1.purchase_date AS "Purchased",
(
SELECT p1.product_name
FROM WarrDBO.products p1 WITH(NOLOCK)
WHERE p1.product_id = i1.product_id
) AS "Product Purchased",
STUFF(
(
SELECT
', ' + a.name
FROM [table-valued-function](i1.accessories) acc_list
INNER JOIN accessories a ON acc_list.id = a.id
FOR XML PATH('')
), 1, 1, ''
) AS [accessories]
FROM WarrDBO.warranty w1
LEFT OUTER JOIN WarrDBO.warranty_info i1
ON i1.warranty_id = w1.warranty_id
ORDER BY w1.warranty_id ASC
回答by ypercube??
Nothing to do with your question. Just a note that your original query can also be written, moving the subqery to a join, as:
与你的问题无关。请注意，您也可以编写原始查询，将 subqery 移动到连接，如下所示：
SELECT w1.warranty_id AS "No.",
w1.created AS "Register Date"
w1.full_name AS "Name",
w1.purchase_date AS "Purchased",
p1.product_name AS "Product Purchased",
i1.accessories
FROM WarrDBO.warranty w1
INNER JOIN WarrDBO.products p1
ON p1.product_id = i1.product_id
LEFT OUTER JOIN WarrDBO.warranty_info i1
ON i1.warranty_id = w1.warranty_id
ORDER BY w1.warranty_id ASC
回答by Paul Sasik
You just need to use the FOR XMLfeature of SQL Server to easily cat strings:
你只需要使用SQL Server的FOR XML特性来轻松地对字符串进行分类：
Example from the linked blog post:
来自链接博客文章的示例：
SELECT
STUFF(
(
SELECT
' ' + Description
FROM dbo.Brands
FOR XML PATH('')
), 1, 1, ''
) As concatenated_string
To parse a field that has already been stored as comma delimited you will have to write a UDF that parses the field and returns a table which can then be used with an IN predicate in your WHERE clause. Look here for starters, and here.
要解析已存储为逗号分隔的字段，您必须编写一个 UDF 来解析该字段并返回一个表，然后该表可以与 WHERE 子句中的 IN 谓词一起使用。看看这里首发，并在这里。
回答by pcofre
It seem to be a work for a concatenate aggregate function. In SQL it can be deployed using CLR
它似乎是连接聚合函数的工作。在 SQL 中，它可以使用 CLR 进行部署
