SQL：嵌套SELECT在单个字段中具有多个值

Question

在我的SQL 2005数据库中，我有一个表格，其值存储为与其他表格有关系的ID。因此，在我的MyDBO.warranty表中，我正在存储product_id而不是product_name以节省空间。 product_name存储在MyDBO.products。

当营销部门拉动的人口统计资料，查询相关表中的每个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 

现在，我的问题是，“附件”列在warranty_info表存储几个值：

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 

我需要做同样的事情与我与“产品”做“附件”，并从拉accessory_name MyDBO.accessories表使用accessory_id。我不知道从哪里开始，因为首先我需要提取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 

我该怎么做？

编辑>>发布我的最终代码：

我创造了这个功能：

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 

并据此更新了我的代码：

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 

Answer 1

您可以编写一个表值函数，它将逗号分隔的字符串拆分为XML，并将XML节点转换为行。

参见： http://www.kodyaz.com/articles//t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

通过函数调用的结果加入到配饰，以及东西，结果回逗号分隔的名称列表。

未经测试的代码：

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 

Answer 2

你只需要使用的功能FOR XML SQL Server轻松地扫描字符串：

来自链接的博客帖子：

SELECT 
    STUFF(
    (
    SELECT 
     ' ' + Description 
    FROM dbo.Brands 
    FOR XML PATH('') 
    ), 1, 1, '' 
) As concatenated_string 

要分析已经被存储为逗号分隔的你将不得不写分析领域，并返回，然后可以用IN谓词在WHERE中使用的表UDF场条款。看看here for starters和here。

Answer 3

无关，与你的问题。只是，你原来的查询，也可以写一个说明，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 

Answer 4

这似乎是一个串连聚合函数工作。 在SQL它可以使用CLR

http://www.mssqltips.com/tip.asp?tip=2022