更新时间:2023-11-26 08:18:10
没办法,这根本不是数据库。首先要了解设计数据库的正确方法:
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.