代码之家 › 专栏 › 技术社区 › John K

在数据库CSV设计错误的基础上与LINQ建立可用的连接关系

linq-to-sql sql-server c#

John K · 技术社区 · 14 年前

我正在寻找一种方法来修复和/或抽象掉数据库字段中的逗号分隔值(comma-separatedvalues,CSV)列表,以便重建一个可用的关系,这样我就可以正确地连接下面的两个表并使用林克 .Join方法 .

下面是一个显示 CsvArticleIds公司 文章记录。

表[dbo][ 人 ]

Id Name       CsvArticleIds
-- ---------- --------
1  Joe        "15,22"
5  Ed         "22"
10 Arnie      "8,15,22"

表[dbo][ 文章 ]

Id Title
-- ----------
8  Beginning C#
15 A Historic look at Programming in the 90s
22 Gardening in January

修复可以在任何级别:C#.NET或sqlserver
不寻找效率,因为这是一次性数据迁移任务的一部分,需要多长时间就需要多长时间。

2 回复 | 直到 14 年前

Mike Jacobs 14 年前

首先将Person表转换为更有用的内容,例如

var newpersons =
    data.Persons.Select(p => new
        {
          Id = p.Id,
          Name = p.Name,
          ArticleIds = p.CsvArticleIds.Substring(1, p.CsvArticleIds.Length -2).Split(',').ToList()
        });

现在您可以加入person.ArticleIds集合。

KM. 14 年前

我将使用SQL在表级别解决这个问题。我将创建一个新表,其中包含person Id和article Id。在填充这个新表之后,我将删除Person.CsvArticleIds列。然后,您将拥有一个规范化的表结构来为人们存储文章。

你需要把CsvArticleIds字符串分开。在SQLServer中有很多方法可以分割字符串。本文介绍了几乎所有方法的优缺点:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL 但是在SQLServer中有很多方法可以分割字符串,请参阅前面的链接,其中解释了每种方法的优缺点。

要使Numbers表方法工作,您需要执行一次性表设置,这将创建一个表 Numbers

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置数字表后,创建此函数:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

现在,您可以轻松地将CSV字符串拆分为一个表并将其联接:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

DECLARE @YourTable table (Id int, Name varchar(10), CsvArticleIds varchar(500))
INSERT @YourTable VALUES (1  ,'Joe'        ,'15,22')
INSERT @YourTable VALUES (5  ,'Ed'         ,'22')
INSERT @YourTable VALUES (10 ,'Arnie'      ,'8,15,22')

DECLARE @YourTableNormalized table (Id int, ArticleId int)

    INSERT INTO @YourTableNormalized
        (Id, ArticleId)
    SELECT 
        y.Id, st.ListValue
        FROM @YourTable y 
            CROSS APPLY  dbo.FN_ListToTable(',',y.CsvArticleIds) AS st
        ORDER BY st.ListValue

SELECT * FROM @YourTableNormalized ORDER BY Id,ArticleId

输出:

Id          ArticleId
----------- -----------
1           15
1           22
5           22
10          8
10          15
10          22

(6 row(s) affected)