如何在逗号分隔值上进行内连接 | 如何在逗号分隔值上进行内连接

本文介绍了如何在逗号分隔值上进行内连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

There are one table         Table 2
ID        Country NAME      Person       Visited Country
1         India             ABC          1,2,3
2         USA               PQR          2,3
3         Brazil            XYZ          1,3

我想选择以下结果

I want select result as below

ABC                    India,USA,Brazil
PQR                    USA,Brazil
XYZ                    India,Brazil

推荐答案

SELECT '1' [ID] , 'India'  [Country NAME]
INTO [#Country]
UNION ALL
SELECT '2' [ID] , 'USA'    [Country NAME]
UNION ALL
SELECT '3' [ID] , 'Brazil' [Country NAME]
;
SELECT 'ABC' [Person] , '1,2,3' [Visited Country]
INTO [#Person]
UNION ALL
SELECT 'PQR' [Person] , '2,3'   [Visited Country]
UNION ALL
SELECT 'XYZ' [Person] , '1,3'   [Visited Country]
;
WITH [JOIN] AS
(
  SELECT [Person] , '<' + REPLACE([Visited Country],',','>,<') + '>' [Visited Country] FROM [#Person]
UNION ALL
  SELECT [Person]
  , REPLACE(A.[Visited Country],'<'+B.ID+'>',B.[Country NAME])
  FROM [JOIN] A
  INNER JOIN [#Country] B
  ON A.[Visited Country] LIKE '%<' + B.ID + '>%'
)
SELECT DISTINCT [Person]
, [Visited Country]
FROM [JOIN]
WHERE [Visited Country] NOT LIKE '%<%>%'
;
DROP TABLE [#Country]
DROP TABLE [#Person]

请为了爱仓鼠，在存储和存储之前将列表拆分为正常化的方式。

Please, for the love of the hamsters, split the list before storing and store in a normalized fashion.

这篇关于如何在逗号分隔值上进行内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！