sql-server - 在设计不良的模式中进行复杂查询的替代方法

Question

我正在努力将来自第 3 方系统的一些数据集成到我的一个应用程序中(基于 ASP Classic 的传统 Web 应用程序/SQL 2000) - 他们在方法方面做出了一些糟糕的决定(恕我直言)和数据结构，尽管也许我们可能有机会在某个时候进行重构……但在那之前，我必须处理眼前的事情。

主表保存检查数据，其中一个字段用于记录是否观察到某些特征。这些特征存储在名为 Categories 的表中，但不幸的是，主检查表 (Test) 通过连接相关的 CategoryID 链接到类别到单个字段(SelectedCategories)。因此，例如，如果观察到特征 01 和 02，则 Test 中该行的 SelectedCategories 列的值为“01C02C”。

修剪后的 DDL:

CREATE TABLE [dbo].[Test](
[ItemID] [varchar](255) NOT NULL,
[Result] [varchar](255) NULL,
[Comments] [varchar](255) NULL,
[ResultReason] [varchar](255) NULL,
[ImageLocation] [varchar](255) NULL,
[TestDateTime] [smalldatetime] NOT NULL,
[SelectedCategories] [varchar](255) NULL)

问题是，在这种情况下，我如何才能最好地从 Test 中提取数据并分割观察到的特征？

我想要的客户端输出是一个包含以下列的表:Test.PK、Test.Field2...Test.Fieldn、类别.ID1、类别.ID2、类别.IDn

这可能还不够清楚 - 第一个字段是 Test 中的常见字段，后面是 中每个类别的勾号或叉号(或其他一些视觉指示符)类别。

显然，如果可以在一次查询中实现这一点，那么在效率和性能方面就更好了。但是，我不确定如何实现这一点 - 如何通过 SelectedCategories 加入 Categories 表？

我显然可以简单地报告SelectedCategories值并让应用程序解析该值。这可能是硬编码的，或者更有可能我们会为测试中的每一行重新查询类别 - 尽管这会对性能产生影响。说实话，在这种情况下，性能可能不是问题，但仅仅因为你可以侥幸逃脱某些事情，并不意味着你应该养成它的习惯。

同样，如果我有机会重构第 3 方应用程序，我是否会删除 SelectedCategories 列并添加 TestCategories 表？或者我会将每个类别硬编码为一系列位列。在系统的整个生命周期中，类别很可能不会发生变化，但如果发生变化，则意味着数据库和应用程序都会发生变化(尽管非常微小)。

我希望我已经解释得足够清楚了。本质上，我是说如果我坚持使用当前系统，最好的方法是什么？如果我要重构，我可以采取什么不同的方法？

进度更新:

非常感谢 Lieven，到目前为止我已经得到了:

DML:

SELECT  c.ID, c.Category, t.FilterID, t.OperatorResult, t.SelectedCategories
FROM    dbo.Categories c
        inner JOIN dbo.Test t ON CHARINDEX(Cast(c.ID as varchar), t.SelectedCategories, 1) <> 0
order by FilterID, ID

输出:

ID   Category             FilterID   OperatorResult   SelectedCategories
4    Cracked Ceramic      137667     FAILED           04C
4    Cracked Ceramic      284821     FAILED           04C
4    Cracked Ceramic      287617     FAILED           04C05C
5    Damaged Case         287617     FAILED           04C05C
4    Cracked Ceramic      310112     FAILED           04C05C
5    Damaged Case         310112     FAILED           04C05C

这就足够了，除了为了达到我想要的屏幕输出......

Filter ID  Operator Result    Cat Matl   Crack    Damage   High Soot   
137667     FAILED             X          X        
178643     FAILED           
284821     FAILED                        X        
287617     FAILED                                 X        X     
310112     FAILED                        X        X    

...我要么需要对 SQL 进行进一步的工作(以便我可以在一个查询中实现所需的输出)，要么需要在应用程序本身中做一些额外的工作。

结论:

如果我们看一下 Lieven 的最新示例(如下)，我们可以看到该问题可以在 TSQL 中解决，但类别是硬编码的。

另一种选择是坚持原始数据并让 IIS/ASP 做更多工作。它肯定会使源代码变得复杂，但如果添加或删除类别，则会消除更新 TSQL 的潜在开销。我当然可以忍受这种偶尔需要更新 TSQL 的情况，但我正在提前考虑不同的问题，其中类别表将定期主动更改。

Answer 1

通过SelectedCategories加入Categories表可以像这样工作

编辑

一些值得思考的事情

• 虽然 group by 和 all 可以实现交叉表功能，但也许最好在客户端应用程序中处理此问题。
• 您在问题中提供的输入与您提供的输出不匹配。我已经使用了您的输入并假设了有关所请求输出的一些内容。我假设 Cat Matl 和 High Soot 只是类别的其他可能值。

请告诉我们它是否适合您。

BEGIN TRAN

CREATE TABLE [dbo].[Categories](
[CategorieID] INTEGER NOT NULL)

CREATE TABLE [dbo].[Test](
[ItemID] [varchar](255) NOT NULL,
[Result] [varchar](255) NULL,
[Comments] [varchar](255) NULL,
[ResultReason] [varchar](255) NULL,
[ImageLocation] [varchar](255) NULL,
[TestDateTime] [smalldatetime] NOT NULL,
[SelectedCategories] [varchar](255) NULL)

INSERT INTO dbo.Categories VALUES (4)
INSERT INTO dbo.Categories VALUES (5)


INSERT INTO dbo.Test VALUES (137667, 'FAILED', NULL, 'Cracked Ceramic', NULL, GetDate(), '04C')
INSERT INTO dbo.Test VALUES (284821, 'FAILED', NULL, 'Cracked Ceramic', NULL, GetDate(), '04C')
INSERT INTO dbo.Test VALUES (287617, 'FAILED', NULL, 'Cracked Ceramic', NULL, GetDate(), '04C05C')
INSERT INTO dbo.Test VALUES (287617, 'FAILED', NULL, 'Damaged Case'   , NULL, GetDate(), '04C05C')
INSERT INTO dbo.Test VALUES (310112, 'FAILED', NULL, 'Cracked Ceramic', NULL, GetDate(), '04C05C')
INSERT INTO dbo.Test VALUES (310112, 'FAILED', NULL, 'Damaged Case'   , NULL, GetDate(), '04C05C')

SELECT  [Filter ID] = t.ItemID
        , [Operator Result] = t.Result
        , [Reason] = t.ResultReason
INTO    #Output
FROM    dbo.Categories c                  
        LEFT OUTER JOIN dbo.Test t ON 
          /* Search for "C<{00}CategorieID>C" */
          CHARINDEX('C'                                                       -- Prefix CategorieID & SelectedCategories with 'C'
                      + REPLICATE('0', 2 - LEN(CAST(CategorieID AS VARCHAR))) -- Left Pad CategorieID with '0'
                      + CAST(CategorieID AS VARCHAR)                          -- Add CategorieID itself
                      + 'C'                                                   -- Suffix search string with 'C'.
                    , 'C' + t.SelectedCategories                              -- Prefix CategorieID & SelectedCategories with 'C'
                    , 1) <> 0

SELECT    [Filter ID]
          , [Operator Result]
          , [Cat Matl] = CASE WHEN [Cat Matl] = 1 THEN 'X' ELSE '' END
          , [Crack] = CASE WHEN [Crack] = 1 THEN 'X' ELSE '' END
          , [Damage] = CASE WHEN [Damage] = 1 THEN 'X' ELSE '' END
          , [High Soot] = CASE WHEN [High Soot] = 1 THEN 'X' ELSE '' END
FROM      (
            SELECT    [Filter ID]
                      , [Operator Result]
                      , [Cat Matl] = MAX(CASE WHEN Reason = 'Cat Matl' THEN 1 ELSE 0 END) 
                      , [Crack] = MAX(CASE WHEN Reason = 'Cracked Ceramic' THEN 1 ELSE 0 END) 
                      , [Damage] = MAX(CASE WHEN Reason = 'Damaged Case' THEN 1 ELSE 0 END) 
                      , [High Soot] = MAX(CASE WHEN Reason = 'High Soot' THEN 1 ELSE 0 END) 
            FROM      #Output
            GROUP BY  [Filter ID]
                      , [Operator Result]
          ) o

DROP TABLE #Output

ROLLBACK TRAN