如何创建具有串联列表和唯一行的视图

hlswsv35 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(317)

使用ssms18，我试图创建一个连接三个表的视图。

[Client_Info]
+----------+-----------+------------+
| ClientID |  LastName | FirstName  |
+----------+-----------+------------+
|    1     | Smith     | John       |
|    2     | Doe       | Jane       |

[Products_Ordered]
+----------+-------------+
| ClientID |  ProductID  |
+----------+-------------+
|    1     |     111     |
|    1     |     222     |
|    2     |     111     |

[Product_Info] 
+-----------+--------------+
| ProductID |  ProductName |
+-----------+--------------+
|    111    |    Apples    |
|    222    |    Oranges   |

我想以下面的输出结束，其中产品被连接到每个客户机的列表中。

+----------+-----------+------------+------------------+
| ClientID |  LastName | FirstName  |     Products     |
+----------+-----------+------------+------------------+
|    1     | Smith     | John       | Apples, Oranges  |
|    2     | Doe       | Jane       | Apples           |

到目前为止，这就是我所拥有的

CREATE VIEW [dbo].[uvw_summary]
AS
SELECT A.[ClientID]  
         , A.[LASTNAME]                                           
        , A.[FIRSTNAME]                                          
        ,            (
                     SELECT  CONVERT(VARCHAR(MAX), C.[ProductName]) + ', '
                     FROM  [Products_Ordered] AS B 
                                         JOIN [Product_Info] AS C 
                                         ON B.[ProductID] = C.[ProductID]
                     WHERE   A.[ClientID]  = B.[ClientID]
                     ORDER BY B.[ProductID] 
                     FOR XML PATH ('')
              ) AS [Products]
FROM     [Client_Info] AS A 
                     JOIN [Products_Ordered] AS B 
                     ON  A.[ClientID] = B.[ClientID]
                     JOIN [Product_Info] AS C 
                     ON B.[ProductID] = C.[ProductID]

使用现有代码，当每个客户机有多个订单时，我会得到两行。

+----------+-----------+------------+------------------+
| ClientID |  LastName | FirstName  |     Products     |
+----------+-----------+------------+------------------+
|    1     | Smith     | John       | Apples, Oranges  |
|    1     | Smith     | John       | Apples, Oranges  |
|    2     | Doe       | Jane       | Apples           |

sql sql-server

来源：https://stackoverflow.com/questions/62568692/how-to-create-view-with-concatenated-list-and-only-unique-rows

1条答案

按热度按时间

dnph8jn41#

只需删除外部查询中的联接，它们是冗余的：

CREATE VIEW [dbo].[uvw_summary]
AS
SELECT   A.[ClientID]  
       , A.[LASTNAME]                                           
       , A.[FIRSTNAME]                                          
       , (    SELECT CONVERT(VARCHAR(MAX), C.[ProductName]) + ', '
              FROM   [Products_Ordered] AS B 
                     JOIN [Product_Info] AS C 
                         ON B.[ProductID] = C.[ProductID]
              WHERE   A.[ClientID]  = B.[ClientID]
              ORDER BY B.[ProductID] 
              FOR XML PATH ('')
         ) AS [Products]
FROM     [Client_Info] AS A;

它也可能值得使用 TYPE 随着 .value() 这样当字符串包含特殊的xml字符时就不会出现问题

CREATE VIEW [dbo].[uvw_summary]
AS
SELECT   A.[ClientID]  
       , A.[LASTNAME]                                           
       , A.[FIRSTNAME]                                          
       , (    SELECT CONVERT(VARCHAR(MAX), C.[ProductName]) + ', '
              FROM   [Products_Ordered] AS B 
                     JOIN [Product_Info] AS C 
                         ON B.[ProductID] = C.[ProductID]
              WHERE   A.[ClientID]  = B.[ClientID]
              ORDER BY B.[ProductID] 
              FOR XML PATH (''), TYPE
         ).value('.', 'NVARCHAR(MAX)') AS [Products]
FROM     [Client_Info] AS A;

最后，您说您使用的是ssms 18，这实际上并不意味着什么，因为ssms是一个用户界面，并且没有告诉我们您使用的sql server版本的任何信息，但是如果您使用的是更新版本的ssms，那么您可能使用的是更新版本的sql server，如果您使用的是，您可以使用 STRING_AGG :

CREATE VIEW [dbo].[uvw_summary]
AS
SELECT  A.[ClientID]  
      , A.[LASTNAME]                                           
      , A.[FIRSTNAME]                                          
      , STRING_AGG(C.[ProductName], ',') WITHIN GROUP ORDER BY c.ProductName)
FROM    [Client_Info] AS A
        JOIN [Products_Ordered] AS B 
            ON A.[ClientID] = B.[ClientID]
        JOIN [Product_Info] AS C 
            ON B.[ProductID] = C.[ProductID]
GROUP BY a.[ClientID], A.[LASTNAME], A.[FIRSTNAME];

赞(0）回复(0）举报 2021-07-24

我来回答

如何创建具有串联列表和唯一行的视图

1条答案

相关问题

热门标签

最新问答