SQL Server ГДЕ В двух разных массивах
Пожалуйста, помогите мне выучить SQL! В приведенном ниже коде в последней строке у меня есть WHERE, который должен фильтровать данные в соответствии с элементами в массивах. Это отлично работает, если в массивах есть только один элемент, но для большего количества результатов он дает 0. Массивы попадают в хранимую процедуру из параметров @Region и @City.
Как я могу заставить этот код работать с несколькими элементами в массивах?
DECLARE @act nvarchar(50) = 'salesbyregionandcity';
DECLARE @Region nvarchar(MAX) = 'Marrakech-Safi,Rabat-Salé-Kénitra';
DECLARE @City nvarchar(MAX) = 'Marrakesh,Salé';
IF(LOWER(@act) = 'salesbyregionandcity')
SELECT
r.[Name] AS RegionName, x.CityName, x.Amount, x.[Year]
FROM
(
SELECT
c.IdRegion, c.[Name] AS CityName, b.Amount, b.[Year]
FROM
(
SELECT
ISNULL(c.IdCity, 1) AS IdCity, a.IdCustomerPos,
a.Amount, a.[Year]
FROM
(
SELECT
IdCustomerPos, Amount, Year([Date]) AS [Year]
FROM
Invoice
WHERE
YEAR([Date]) = YEAR(getdate())
OR YEAR([Date]) = YEAR(getdate()) - 1
)
AS a
LEFT JOIN
CustomerOffices c
ON a.IdCustomerPos = c.IdCustomer
)
AS b
LEFT JOIN
City c
ON c.Id = b.IdCity
WHERE
c.[Name] = @City
)
AS x
LEFT JOIN
Region r
ON x.IdRegion = r.Id
WHERE
r.[Name] IN (SELECT * FROM STRING_SPLIT (@Region, ','))
AND x.CityName IN (SELECT * FROM STRING_SPLIT (@City, ','))
Это не массивы. Это струны, которые вы пытаетесь разделить. Если вы хотите использовать несколько значений, вы можете использовать параметры или переменные табличного типа. Фактически, вы должны сделать это с этим запросом вместо разделения строк внутри запроса, просто чтобы облегчить понимание
Используйте разделитель строк (String_split (Transact-SQL), delimitedsplit8k_lead, XML Splitter) или используйте переменную / параметр табличного типа.
@Larnu код уже использует STRING_SPLITвнутри запрос. "Массивы" - наименьшая проблема, запрос требует серьезной очистки.
@CristiPriciu, что вы пытаетесь сделать с этим запросом? Для создания отчетов по годам и городам не нужен такой сложный код. Вы, вероятно, не должен создаете отчеты поверх транзакционных таблиц, таких как счета-фактуры. Простая звездная схема сделала бы такой запрос тривиальным и молниеносным. Даже без звездообразной схемы таблица измерений «Дата / Календарь», «География» и «Бизнес-единица» упростит агрегирование много.
@PanagiotisKanavos, ты прав, я даже не видел этого в том бардаке.
Что есть в @CITY? Может ли это быть связано с ведущими пробелами? например «Лондон, НЬЮ-ЙОРК». Я думаю, ваш STRING_SPLIT так или иначе не возвращает то, что вы ожидали - попробуйте его по отдельности и посмотрите, что он вернет
Спасибо за ваши ответы. Я просто новичок, пытаюсь заставить это работать. Да, никаких массивов :) Цель этой хранимой процедуры - возвращать данные, отфильтрованные по региону и городу. @City содержит названия городов. Марракеш и Сале находятся в городах Марокко.
@CITY разделен запятыми, например "Марракеш, распродажа"? Вы должны использовать правильный разделитель
Я предлагаю вам сначала запустить запрос без последнего левого соединения с таблицей регионов. LEFT JOIN Region r ON x.IdRegion = r.Id WHERE r.[Name] IN (SELECT * FROM STRING_SPLIT (@Region, ',')) AND x.CityName IN (SELECT * FROM STRING_SPLIT (@City, ',')) И затем исследуйте результат, посмотрите на столбец CityName, имеет ли этот столбец значение (или оно пустое)? Думаю, так вы найдете решение для своего дела.
Изложите свой код лучше, тогда вы сможете увидеть уровни вложенности. И такие вещи, как ваша ошибка, не прячутся в беспорядке ... WHERE c.[Name] = @City
Да MatBailie, только что заметил это за несколько секунд до вашего поста, пока делал то, что подсказал икрам. Спасибо всем за ваш вклад!
Ответы 2
Я бы использовал переменные таблицы для решения этой проблемы, например:
declare @temp table (value nvarchar(255))
insert into @temp values ('value1'), ('value2') --,('value3')...
Чтобы использовать в запросе «фильтрующие» таблицы, достаточно сделать следующее:
select * from [table_name] where [column_name] in (select value from @temp)
Надеюсь это поможет.
Удалите следующие ...
WHERE
c.[Name] = @City
Ваша переменная @city - это список с разделителями-запятыми, который обрабатывается в самом внешнем предложении WHERE. Я предполагаю, что это задержка до того, как вы попали в «список».
Если бы вы изложили свой код менее "компактно", его было бы легче заметить.
Кроме того, поскольку NULL может быть никогда чем угодно, ваш IN() действительно должен быть LEFT JOIN.
Что касается форматирования, то в этом нет никакой необходимости.
SELECT
r.[Name] AS RegionName,
c.[Name] AS CityName,
i.Amount,
Year(i.[Date]) AS [Year]
FROM
Invoice AS i
INNER JOIN
CustomerOffices AS o
ON i.IdCustomerPos = o.IdCustomer
INNER JOIN
City AS c
ON c.Id = ISNULL(o.IdCity, 1)
AND c.[Name] IN (SELECT * FROM STRING_SPLIT (@City, ','))
INNER JOIN
Region AS r
ON r.id = c.IdRegion
AND r.[Name] IN (SELECT * FROM STRING_SPLIT (@Region, ','))
WHERE
YEAR(i.[Date]) = YEAR(getdate())
OR YEAR(i.[Date]) = YEAR(getdate()) - 1
Два последних совета ...
Обычно лучше использовать INNER JOIN для результатов JOIN и других "функций табличных значений", поскольку производительность STRING_SPLIT()может резко ухудшается по мере увеличения размера списка ...
INNER JOIN
City AS c
ON c.Id = ISNULL(o.IdCity, 1)
INNER JOIN
STRING_SPLIT(@City, ',') AS c_list
AND c.[Name] = c_list.value
INNER JOIN
Region AS r
ON r.id = c.IdRegion
INNER JOIN
STRING_SPLIT(@Region, ',') AS r_list
AND r.[Name] = r_list.value
Наконец, если у вас есть индекс на IN(), ваш текущий пункт Invoice(Date)не могу использует его.
Как правило, старайтесь не помещать какие-либо вычисления в столбец, который вы ищете, вместо этого сохраняйте вычисления в правой части.
Например, ниже представлены все счета с датами 1 января прошлого года или позже.
WHERE
i.Date >= DATEFROMPARTS(YEAR(GETDATE())-1, 1, 1)
Спасибо, Мэтт! Только одна маленькая вещь: c.IdCustomer должен быть o.IdCustomer.
@CristiPriciu, обратите внимание, как легко найти ошибки, если код написан правильно.
В вашем коде нет массивов. SQL Server их не поддерживает.