#sql #json #sql-server #qsqlquery #unnest
#sql #json #sql-сервер #qsqlquery #unnest
Вопрос:
У меня есть таблица, подобная
| Имя | Итого | Дата |
|---|---|---|
| A | 10 | 2020-12-01 |
| B | 5 | 2020-12-01 |
| A | 10 | 2020-12-02 |
| B | 15 | 2020-12-02 |
теперь у меня есть список имен и список дат, например
@NameList = '[A],[B],[C],[D]'
@DateList = '[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04]'
Как выполнить запрос для извлечения данных для каждого имени и даты, доступных в @NameList and @DateList ?
Ожидаемый результат
| Имя | Итого | Дата |
|---|---|---|
| A | 10 | 2020-12-01 |
| B | 5 | 2020-12-01 |
| C | 0 | 2020-12-01 |
| D | 0 | 2020-12-01 |
| A | 10 | 2020-12-02 |
| B | 15 | 2020-12-02 |
| c | 0 | 2020-12-02 |
| D | 0 | 2020-12-02 |
| A | 0 | 2020-12-03 |
| B | 0 | 2020-12-03 |
| c | 0 | 2020-12-03 |
| D | 0 | 2020-12-03 |
| A | 0 | 2020-12-04 |
| B | 0 | 2020-12-04 |
| c | 0 | 2020-12-04 |
| D | 0 | 2020-12-04 |
Комментарии:
1. Привет @DaleK, список имен и ранд даты поступают из пользовательского интерфейса
Ответ №1:
Как насчет подхода JSON?
Предполагая, что имена и даты никогда не содержат двойных кавычек, вы можете превратить их в строки JSON с помощью строковых функций и отменить каждый список в виде строк с openjson() помощью . Последний шаг — привести исходную таблицу с помощью a left join .
declare @NameList nvarchar(max) = '[A],[B],[C],[D]';
declare @DateList nvarchar(max) = '[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04]';
select n.name, coalesce(t.total, 0) as total, d.dt
from openjson('[' replace(replace(@NameList, '[', '"'), ']', '"') ']')
with(name nvarchar(max) '
Ответ №2:
Вы можете разбить списки, используя string_split() , а затем использовать left join для ввода существующих данных:
declare @NameList nvarchar(max) = '[A],[B],[C],[D]';
declare @DateList nvarchar(max) = '[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04]';
select v.name, v.date, coalesce(t.total, 0) as total
from string_split(@namelist, ',') n cross join
string_split(@datelist, ',') d cross apply
(values (replace(replace(n.value, '[', ''), ']', ''),
convert(date, replace(replace(d.value, '[', ''), ']', ''))
)
) v(name, date) left join
yourtable t
on t.name = v.name and t.date = v.date
Единственный реальный трюк здесь - использовать apply для удаления квадратных скобок.
Вот скрипка db<> .
Ответ №3:
используйте это
select t1.name,t2.[date] from
[table] t1
cross apply [table] t2
Комментарии:
1. Пожалуйста, проиллюстрируйте, как это решает проблему OPs? Похоже, что он не учитывает ни один из соответствующих диапазонов.
2. Хотя этот код может ответить на вопрос, предоставление дополнительного контекста относительно того, как и / или почему он решает проблему, улучшит долгосрочную ценность ответа.
) n
cross join openjson('[' replace(replace(@DateList, '[', '"'), ']', '"') ']')
with(dt date '
Ответ №2:
Вы можете разбить списки, используя string_split() , а затем использовать left join для ввода существующих данных:
Единственный реальный трюк здесь - использовать apply для удаления квадратных скобок.
Вот скрипка db<> .
Ответ №3:
используйте это
Комментарии:
1. Пожалуйста, проиллюстрируйте, как это решает проблему OPs? Похоже, что он не учитывает ни один из соответствующих диапазонов.
2. Хотя этот код может ответить на вопрос, предоставление дополнительного контекста относительно того, как и / или почему он решает проблему, улучшит долгосрочную ценность ответа.
) d
left join mytable t on t.name = n.name and t.date = d.dt
Ответ №2:
Вы можете разбить списки, используя string_split() , а затем использовать left join для ввода существующих данных:
Единственный реальный трюк здесь — использовать apply для удаления квадратных скобок.
Вот скрипка db<> .
Ответ №3:
используйте это
Комментарии:
1. Пожалуйста, проиллюстрируйте, как это решает проблему OPs? Похоже, что он не учитывает ни один из соответствующих диапазонов.
2. Хотя этот код может ответить на вопрос, предоставление дополнительного контекста относительно того, как и / или почему он решает проблему, улучшит долгосрочную ценность ответа.