使用 SQL2000 将字段中的数据拆分为行

Question

请帮我找到解决方案。我的表中有数据，就像

ID    Code      

1     123,456,789,12  
2     456,073          
3     69,76,56

我需要在行中列出代码

ID    Code                 Ref

1     123,456,789,12       123

1     123,456,789,12       456

1     123,456,789,12       789

1     123,456,789,12       12

2     456,073              456

2     456,073              073

3     69,76,56             69

3     69,76,56             76

3     69,76,56             56

一样，如何在查询命令中执行此操作？我将使用 ref 列中的值来连接另一个表中的另一列。 感谢支持

Answer 1

我的第一个建议是标准化你的数据库。一列应该包含一条信息。您的逗号分隔值违反了此规则，这就是您面临如此困难的原因。但由于人们很少采纳这个建议，这里有一个可能对你有用的拼凑。由于您要将其连接到另一个表，因此您实际上不需要将其自己的列中的每个值分开，您只需要能够在您的列中找到匹配的值：

SELECT
    T1.id,
    T1.code,
    T2.ref
FROM
    My_Table T1
INNER JOIN Table_I_Am_Joining T2 ON
    T1.code LIKE '%,' + CAST(T2.ref AS VARCHAR(20)) + ',%' OR
    T1.code LIKE CAST(T2.ref AS VARCHAR(20)) + ',%' OR
    T1.code LIKE '%,' + CAST(T2.ref AS VARCHAR(20)) OR
    T1.code = CAST(T2.ref AS VARCHAR(20))

这依赖于您的列中的代码采用精确的格式，以逗号分隔，不带空格。如果情况并非如此，那么这可能不会返回您想要获得的结果。

Answer 2

答案是标准化您的数据库。

同时，在大型集上表现更好的一种解决方法是使用临时表。 （LIKE 搜索不能使用索引）

此方法还显示了标准化数据和处理空格的一些步骤。

首先创建一个“Tally Table”（如果没有） 这是一次性交易，Tally 表对于 全部  种类 事物。

/*--- Create a Tally table.  This only needs to be done once.
    Note that "Master.dbo.SysColumns" is in all SQL 2000 installations.
    For SQL 2005, or later, use "master.sys.all_columns".
*/
SELECT TOP 11000   -- Adequate for most business purposes.
    IDENTITY (INT, 1, 1)    AS N
INTO
    dbo.Tally
FROM
    Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2

--- Add a Primary Key to maximize performance.
ALTER TABLE     dbo.Tally
ADD CONSTRAINT  PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

---

现在假设您的表是：

CREATE TABLE ListO_Codes (ID INT IDENTITY(1,1), Code VARCHAR(88))

INSERT INTO ListO_Codes (Code)
SELECT      '123,456,789,12'        UNION ALL
SELECT      '456,073'               UNION ALL
SELECT      '69,76,56'


CREATE TABLE AnotherTable (ID INT IDENTITY(1,1), Ref VARCHAR(8), CodeWord VARCHAR (88))

INSERT INTO AnotherTable (Ref, CodeWord)
SELECT      '12',   'Children'      UNION ALL
SELECT      '123',  'of'            UNION ALL
SELECT      '456',  '-'             UNION ALL
SELECT      '789',  'sun,'          UNION ALL
SELECT      '073',  'see'           UNION ALL
SELECT      '56',   'your'          UNION ALL
SELECT      '69',   'time'          UNION ALL
SELECT      '76',   'has'

---

那么临时表是：

CREATE TABLE    #NORMALIZED_Data (LOD_id INT, Ref int)  -- Make Ref varchar if it's not numeric
INSERT INTO
    #NORMALIZED_Data (LOD_id, Ref)
SELECT
    L.ID,
    -- Split Code string using Tally table and Delimiters
    LTrim (RTrim (SUBSTRING (',' + L.Code + ',', T.N+1, CHARINDEX (',', ',' + L.Code + ',', T.N+1) - T.N - 1 ) ) )
FROM
    dbo.Tally       T,
    ListO_Codes     L
WHERE
    T.N < LEN (',' + L.Code + ',')
AND
    SUBSTRING (',' + L.Code + ',', T.N, 1)   = ','


--- Index for performance
CREATE CLUSTERED INDEX CL_NORMALIZED_Data_LOD_id_Ref
ON #NORMALIZED_Data (LOD_id, Ref) WITH FILLFACTOR = 100

---

然后搜索是：

SELECT
    L.ID,
    L.Code,
    A.Ref,
    A.CodeWord
FROM
    #NORMALIZED_Data    N
INNER JOIN
    ListO_Codes         L   ON N.LOD_id = L.ID
LEFT JOIN
    AnotherTable        A   ON N.Ref    = A.Ref
ORDER BY
    L.ID,
    A.Ref

---

结果是：

ID    Code              Ref    CodeWord
--    --------------    ---    --------
1     123,456,789,12    12     Children
1     123,456,789,12    123    of
1     123,456,789,12    456    -
1     123,456,789,12    789    sun,
2     456,073           073    see
2     456,073           456    -
3     69,76,56          56     your
3     69,76,56          69     time
3     69,76,56          76     has