Блог

Главная - Вопросы по программированию - Поиск совпадающих значений между двумя таблицами при использовании перекрестного применения в SQL

Поиск совпадающих значений между двумя таблицами при использовании перекрестного применения в SQL

#sql #sql-server #join #case #patindex

#sql #sql-server #Присоединиться #случай #patindex

Вопрос:

Я пытаюсь извлечь 5 или более последовательных чисел из столбца x таблицы A и сопоставить извлеченные числа со столбцом z таблицы B. Если значение существует, это нормально, но если значение не существует, его необходимо вставить в таблицу B. Мне удалось извлечь числа из таблицы A , но я застрял при попытке JOIN , потому что я использую CROSS APPLY и CASE . Возможно, я просто не понимаю, как это сделать.

Код, который я использую для извлечения чисел:

 SELECT nvt.AdditionalInformation,
       CASE
           WHEN M.FirstMatch > 0
           THEN SUBSTRING(AdditionalInformation, M.FirstMatch-1, N.SecondMatch-1)
          
           --check IF TPNumber EXISTS in ChangeRequests table then add ChangeRequest Id to ReportVersionChangeRequests table
           ELSE NULL
       END
FROM
(
    SELECT ':' nvt.AdditionalInformation ':' AdditionalInformation
    FROM dbo.NSReportVtest nvt
) nvt

CROSS APPLY(VALUES(PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ':' nvt.AdditionalInformation ':'))) M(FirstMatch)
CROSS APPLY(VALUES(PATINDEX('%[^0-9]%', SUBSTRING(AdditionalInformation, M.FirstMatch-1, LEN(AdditionalInformation))))) N(SecondMatch)

На данный момент это результат работы кода:

Таблица A:

Дополнительная информация (Имя столбца отсутствует)
:Результаты испытаний: NULL
:Результаты испытаний: 256985
:Результаты испытаний: NULL
:Результаты испытаний: NULL
:Результаты испытаний: NULL
:Результаты испытаний: 85965

Ожидаемые результаты:

Таблица A:

Дополнительная информация (Имя столбца отсутствует)
:Результаты испытаний: NULL
:Результаты испытаний: 256985
:Результаты испытаний: NULL
:Результаты испытаний: NULL
:Результаты испытаний: NULL
:Результаты испытаний: 85965

Таблица B:

ID Число
1 61758
2 85965
3 56456
4 78945

Ожидаемый результат после Join

Таблица C:

ID Число
1 61758
2 85965
6 56456
8 78945
9 256985 (добавлена запись)

Комментарии:

1. Я заблудился. Запрос возвращает один результирующий набор. Вам нужно несколько наборов результатов? Вы хотите изменить таблицу b? Вы хотите получить результаты в новом результирующем наборе? Как id вычисляется?

2. Приношу извинения за то, что это немного сбивает с толку. Мне нужен один набор результатов. id Столбец генерируется автоматически. Мне нужна таблица A и таблица B в основном для объединения в таблицу C. Предоставленный код в настоящее время создает таблицу результирующего набора A.

3. . . Почему id тогда s меняются? Почему бы просто не использовать идентификаторы для b ?

4. Поместите SELECT в третью APPLY и присоединитесь к ней

5. В стороне: ELSE NULL не требуется в a CASE , хотя это делает его немного понятнее

Ответ №1:

 create table NSReportVtest (AdditionalInformation nvarchar(max)) -- Source table
create table NSReportVtest2 (id int identity(1,1) ,Value int) -- destination table


insert into NSReportVtest  --- creating source data 
select 'aaa256985bbb'
union all select 'aasa123456babb'
union all select 'aaga245bfbb'
union all select 'abaa54123bnbb'
union all select 'aaba654987bmbb'
union all select 'aacabybb'


insert into NSReportVtest2(Value) -- creating dest data 
select 123456
union all select 54123 


insert into NSReportVtest2  --- inserting missing data using left join 
select cast( substring(SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation)) ,1,PATINDEX('%[^0-9]%',SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation))) -1 ) as int) Value
 from  NSReportVtest t1
left outer join  NSReportVtest2 t2
on cast( substring(SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation)) ,1,PATINDEX('%[^0-9]%',SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation))) -1 ) as int) = t2.Value
where t2.id is null 
and  cast( substring(SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation)) ,1,PATINDEX('%[^0-9]%',SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation))) -1 ) as int)  <>0

Ответ №2:

Я нашел решение, которое работает. I INSERT INTO #temp таблица, в которой затем я могу выбрать определенные столбцы и объединить их со второй таблицей, что упрощает управление данными.

 SELECT nv.AdditionalInformation, 
       nv.Id,
       CASE
           WHEN M.FirstMatch > 0
           THEN SUBSTRING(AdditionalInformation, M.FirstMatch-1, N.SecondMatch-1)
           ELSE NULL
       END AS ExtractedTP
INTO #temp
FROM
(
    SELECT ':' nv.AdditionalInformation ':' AdditionalInformation, 
           nv.Id
    FROM dbo.NSReportVtest nv
) nv
CROSS APPLY(VALUES(PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ':' nv.AdditionalInformation ':'))) M(FirstMatch)
CROSS APPLY(VALUES(PATINDEX('%[^0-9]%', SUBSTRING(AdditionalInformation, M.FirstMatch-1, LEN(AdditionalInformation))))) N(SecondMatch);

--select and join temp table to ChangeRequests table to see which TP Numbers exist

SELECT t.Id, 
       t.ExtractedTP, 
       nrt.TPNumber, 
       nrt.Id
FROM #temp t
     LEFT JOIN dbo.NSChangeRequestsTest nrt ON t.ExtractedTP = nrt.TPNumber
ORDER BY t.ExtractedTP DESC;