如何在逗号分隔值上进行内连接 [英] How to do Inner join on comma separated value

问题描述

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

推荐答案

没办法，这根本不是数据库。首先要了解设计数据库的正确方法：

1. 关系数据库设计 [ ^ ]

2. 设计数据库 [ ^ ]

No way, this is not a database at all. Start by learning the correct way of designing a database:
1. Relational Database Design[^]
2. Designing Databases[^]

SQL中的字符串操作非常低效，你应该避免它们。

特别是构建和拆分逗号分隔列表非常糟糕。



如果我假设SQL Server，那么以下内容将有效，但它将无法很好地扩展 - 相信我。

String operations in SQL are very inefficient and you should avoid them.
Building and splitting comma-separated lists in particular is very bad.

If I assume SQL Server, then the following will work, but it will not scale well -- trust me.

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.

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