sql - 尝试从具有三个表连接的一列连接逗号分隔列表并获取重复项

Question

这是我的表的示例:

项目

+----------+---------------+
| txt_item | txt_unique_id |
+----------+---------------+
| Circle   |             1 |
| Square   |             2 |
| Triangle |             3 |
+----------+---------------+

tag_master

+---------+----------+
| txt_tag | opt_type |
+---------+----------+
| red     | color    |
| blue    | color    |
| yellow  | color    |
| large   | size     |
| medium  | size     |
| small   | size     |
+---------+----------+

item_tags

+---------+---------------+
| txt_tag | txt_unique_id |
+---------+---------------+
| red     |             1 |
| blue    |             1 |
| large   |             1 |
| small   |             1 |
| red     |             2 |
| yellow  |             2 |
| small   |             2 |
| medium  |             2 |
| red     |             3 |
| yellow  |             3 |
+---------+---------------+

我想返回这个:

+----------+----------------------------+
| Circle   | red, blue, large, small    |
| Square   | red, yellow, small, medium |
| Triangle | red, yellow                |
+----------+----------------------------+

这就是我得到的:

+----------+---------------------------------------------+
| Circle   | red, red, red, blue, large, small, small    |
| Square   | red, red, red, yellow, yellow, small, small |
| Triangle | red, red, red, yellow, yellow               |
+----------+---------------------------------------------+

这是我所在的位置:

CREATE TABLE #screening_tags
  (
     txt_unique_id VARCHAR(36),
     tags          VARCHAR(1000)
  )

INSERT INTO #screening_tags
            (txt_unique_id,
             tags)
(SELECT txt_unique_id,
        ( STUFF((SELECT ' , ' + t.txt_tag
                 FROM   item_tags t
                        JOIN tag_master_ tm
                          ON t.txt_tag = tm.txt_tag
                        JOIN items i
                          ON t.txt_unique_id = i.txt_unique_id
                 ORDER  BY opt_type,
                           txt_tage
                 FOR xml path('')), 1, 1, '') )
 FROM   item_tags t)

SELECT *
FROM   #screening_tags

我也尝试过使用 COALESCE，但我遗漏了一些东西。我需要一个 DISTINCT 或其他东西，但我尝试过的一切都不起作用。如果我想按 opt_type 排序，则不能使用 DISTINCT 或 TOP 1。感谢帮助。

Answer 1

尝试将连接删除到 tag_master - 查看您的预期结果，您不需要该表中的任何内容。事实上，您似乎不需要任何连接。

    CREATE TABLE #screening_tags
      (
         txt_unique_id VARCHAR(36),
         tags          VARCHAR(1000)
      )

    INSERT INTO #screening_tags
                (txt_unique_id,
                 tags)
    (SELECT txt_unique_id,
            ( STUFF((SELECT ' , ' + t.txt_tag
                     FROM   items i
                     where t.txt_unique_id = i.txt_unique_id
                     ORDER  BY opt_type,
                               txt_tag
                     FOR xml path('')), 1, 1, '') )
     FROM   item_tags t)

    SELECT *
    FROM   #screening_tags