Я работаю над интеграцией некоторых данных из сторонней системы в одно из моих приложений (ASP прежней версии Основанное на классике веб-приложение / SQL 2000) - они приняли некоторые плохие решения (по моему скромному мнению), когда дело доходит до их подхода и структуры данных, хотя, возможно, мы могли бы получить шанс осуществить рефакторинг в какой-то момент..., но до тех пор, я должен работать с тем, что передо мной.
Основная таблица содержит инспекционные данные с одним из полей, используемых, чтобы записать, если определенные характеристики наблюдались. Характеристики хранятся в названной Таблице Categories
, но к сожалению, основная проверочная таблица (Test
) ссылки на категории путем конкатенации релевантный CategoryID
s в единственное поле (SelectedCategories
). Так, например, если характеристики 01 и 02 наблюдались, SelectedCategories
столбец для той строки в Test
имел бы значение '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
с разбивкой наблюдаемых характеристик?
Вывод на клиенте, который я хочу, является таблицей со следующими столбцами: Тест. PK, Тест. Field2... Тест. Fieldn, Категории. ID1, Категории. ID2, Категории. IDn
Это, вероятно, достаточно не совсем ясно - первые поля были бы обычными подозреваемыми от Test
, сопровождаемый галочкой или крестом (или некоторый другой визуальный индикатор) для каждой из категорий в Categories
.
Очевидно, если это может быть достигнуто в одном запросе, тем лучше с точки зрения эффективности и производительности. Однако я не уверен, как это было бы достигнуто - как Вы присоединитесь Categories
таблица через SelectedCategories
?
Я мог, очевидно, просто сообщить SelectedCategories
оцените и имейте синтаксический анализ приложения значение. Это могло быть трудно кодировано, или более вероятно мы повторно запросим Categories
для каждой строки в Тесте - хотя это имело бы последствия производительности. TBH, производительность, вероятно, не является проблемой в этом случае, но просто потому что можно сойти с рук что-то, не означает, что необходимо сделать привычку к нему.
Аналогичным образом, если у меня есть возможность осуществить рефакторинг приложение сторонних производителей, был бы я удалять SelectedCategories
столбец и добавляет в a TestCategories
таблица? Или был бы я твердый код каждая категория как серия Bit столбцы. По всей вероятности, Categories
не изменится в течение жизни системы, но если они сделали, это означает изменения (хотя очень незначительный) и к DB и к приложению.
Я надеюсь, что объяснил это достаточно ясно. В сущности я говорю, каков лучший подход, если я придерживаюсь существующей системы? И если я должен был осуществить рефакторинг, какой другой подход я мог проявить?
Обновление прогресса:
В основном благодаря 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, но я предполагаю к различным проблемам, где таблица Categories активно изменилась бы регулярно.
Чтобы присоединиться к таблице Categories
через SelectedCategories
, можно поступить следующим образом
Некоторые вещи, о которых следует подумать
Дайте нам знать, если это сработало для вас.
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