TSQL - поиск по нескольким столбцам для одного значения

#tsql #barcode

#tsql #штрих-код

Вопрос:

В нашей базе данных содержится следующая информация об элементе:

 ItemNumber | ItemDescription | CaseUPC        | BoxUPC         | UnitUPC
===============================================================================
12345      | Widget          | 00487060982629 | 00334556905837 | 00803709021138
23456      | Dunkit          | 00442765157512 | 00688083046260 | 00148006723652
34567      | Tiklet          | 00671178066801 | 00181369288501 | 00371783583016

Мы получаем информацию о клиентах, которая довольно часто выглядит следующим образом:

 ItemNumber | UPC
=========================
XPQR       | 487060982629
ROFL       | 688083046260
WTAF       | 371783583016
LOLBBQ     | 123456789012

Поскольку большинство наших клиентов не владеют компьютерной грамотностью и используют Excel для создания CSV-файлов, которые они присылают нам, начальные нули для кодов UPC отсутствуют, хотя они являются действительными кодами.

Используя SQL Server 2012, моя задача — сопоставить код UPC, предоставленный клиентом, с нашей таблицей товаров, чтобы создать отчет, который выглядит следующим образом:

 CustomerItemNumber | OurItemNumber | OurItemDescription | MatchMethod
======================================================================
XPQR               | 12345         | Widget             | CaseUPC
ROFL               | 23456         | Dunkit             | BoxUPC
WTAF               | 34567         | Tiklet             | UnitUPC
LOLBBQ             | --            | --                 | no match found

Я попытался использовать несколько левых соединений из таблицы customer item в нашу таблицу item:

 SELECT C.ItemNumber,
        O.ItemNumber, 
        O.ItemDescription,
        O.CaseUPC,
        O.BoxUPC,
        O.UnitUPC
FROM CustomerItemTable C
    LEFT OUTER JOIN OurItemTable O ON RIGHT('000000000000'   LTRIM(RTRIM(C.UPC)), 12) = RIGHT('000000000000'   LTRIM(RTRIM(O.CaseUPC)), 12)
    LEFT OUTER JOIN OurItemTable O2 ON RIGHT('000000000000'   LTRIM(RTRIM(C.UPC)), 12) = RIGHT('000000000000'   LTRIM(RTRIM(O2.BoxUPC)), 12)
    LEFT OUTER JOIN OurItemTable O3 ON RIGHT('000000000000'   LTRIM(RTRIM(C.UPC)), 12) = RIGHT('000000000000'   LTRIM(RTRIM(O3.UnitUPC)), 12)

…но в итоге я получаю дублирующиеся записи

Ответ №1:

Я не совсем уверен, почему вы снова и снова объединяете одну и ту же таблицу — вы можете использовать одно объединение с условием для нескольких столбцов. Кроме того, вы можете просто избавиться от всех ведущих нулей, приведя к int:

 SELECT C.ItemNumber,
        O.ItemNumber, 
        O.ItemDescription,
        O.CaseUPC,
        O.BoxUPC,
        O.UnitUPC
FROM CustomerItemTable C
LEFT OUTER JOIN OurItemTable O 
ON CAST(C.UPC AS int) IN(CAST(O.CaseUPC AS INT), CAST(O.BoxUPC AS INT), CAST(O.UnitUPC AS INT))

Это, конечно, при условии, что вы хотите объединить таблицы, если UPC в CustomerItemTable соответствует любому из CaseUPC, BoxUPC или UnitUPC в OurItemTable.

1. UPC не является целым числом — оно содержит цифры, но должно быть представлено как строка IMO

2. Спасибо @ZoharPeled, это то, что я искал (небольшие изменения с моей стороны, конечно).

3. @Hogan хотя это и правильно, я предложил преобразование в int только для того, чтобы исключить необходимость ссылаться на начальные нули при сравнении значений.

Ответ №2:

Если вы знаете, что столбец содержит только числовые данные, преобразуйте их в объединении:

 ... CONVERT(INT, UPC) ...

В противном случае используйте LIKE вместо equals:

 ... LIKE upc   '%' ...

Кроме того, вам, вероятно, следует присоединиться к таблице только один раз, используя вместо этого несколько предложений WHERE.