Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Создать полную структуру  [new]
traregs
Member

Откуда:
Сообщений: 15
Задал вопрос в ветке "", ответили про варианты для MySQL, а для MS - сказали, что не там...
traregs
Предположим у меня есть таблица:
A B C
---------
A1 B1 3
A1 B2 500
A2 B3 343
A3 B1 33
A3 B2 50
A3 B3 34

А надо, чтобы получилось:
A B C
---------
A1 B1 3
A1 B2 500
A1 B3 0
A1 B4 0
A2 B1 0
A2 B2 0
A2 B3 343
A2 B4 0
A3 B1 33
A3 B2 50
A3 B3 34
A3 B4 0

то есть, для _каждого_ значения в столбце A - должны быть _все_ варианты B, причём даже те, которые не встречаются в оригинале (просто полный набор, представленный списком констант: 'B1','B2','B3','B4')

Как сделать столбец из констант - придумал, но явно очень неоптимально. А вот как сделать чёткую структуру - не сообразил.


Для MS SQL - предложенный код не работает.
30 янв 17, 14:36    [20162075]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
select
 a.A, b.B, isnull(c.C, 0)
from
 (select distinct A from Таблица) a cross join
 (values ('B1'), ('B2'), ('B3'), ('B4')) b(B) left join
 Таблица c on c.A = a.A and c.B = b.B;
30 янв 17, 14:42    [20162118]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
traregs
Member

Откуда:
Сообщений: 15
invm
select
 a.A, b.B, isnull(c.C, 0)
from
 (select distinct A from Таблица) a cross join
 (values ('B1'), ('B2'), ('B3'), ('B4')) b(B) left join
 Таблица c on c.A = a.A and c.B = b.B;


Бурные, продолжительные аплодисменты, плавно переходящие в овацию!
Это то, что надо. И даже очевидный вариант
(SELECT 'B1' b UNION ALL SELECT 'B2' UNION ALL SELECT 'B3' UNION ALL SELECT 'B4')
- записан правильнее.

То, чего я не знал, это "cross join". Картинка не складывалась без него. Подозреваю, что без него - будет безнадёжно сложно, хотя, может и реально.
30 янв 17, 14:54    [20162195]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31431
traregs
То, чего я не знал, это "cross join". Картинка не складывалась без него. Подозреваю, что без него - будет безнадёжно сложно, хотя, может и реально.
Кросс джойн можно сделать из иннер джойн, написав условие соединения "on 1 = 1"
30 янв 17, 15:55    [20162461]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
traregs
Member

Откуда:
Сообщений: 15
alexeyvg
traregs
То, чего я не знал, это "cross join". Картинка не складывалась без него. Подозреваю, что без него - будет безнадёжно сложно, хотя, может и реально.
Кросс джойн можно сделать из иннер джойн, написав условие соединения "on 1 = 1"


Да,
select
 a.A, b.B, isnull(c.C, 0)
from
 ((select distinct A from [test]) a inner join
 (values ('B1'), ('B2'), ('B3'), ('B4')) b(B) on 1=1) left join
 [test] c on c.A = a.A and c.B = b.B;


-- работает также. Логику я понимаю прекрасно, а вот к тонкостям синтаксиса и к технологии определения типа ошибки - пока не привык. "Неправильный синтаксис около конструкции..." - такой бред, по сравнению с сообщениями отладчика C++/C#. Да ещё и ошибка - может оказаться совсем не "около конструкции", а в другом месте. Совершенно неясно, то ли вообще так нельзя, то ли скобочку забыл.
30 янв 17, 17:11    [20162931]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
traregs
Member

Откуда:
Сообщений: 15
А если вместо [test] - какая-то конструкция (сложный запрос)?

Для примера (только для примера! - на самом деле запрос большой), я заменил "[test]" на "(SELECT * FROM [test]) e"

Вот это - не будет работать:
select
 a.A, b.B, isnull(c.C, 0)
from
 ((select distinct A from (SELECT * FROM [test]) e) a inner join
 (values ('B1'), ('B2'), ('B3'), ('B4')) b(B) on 1=1) left join
 e c on c.A = a.A and c.B = b.B;

Выдаёт: "Недопустимое имя объекта "e"."
30 янв 17, 18:46    [20163285]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
traregs
А если вместо [test] - какая-то конструкция (сложный запрос)?
CTE:
with e as
(
 SELECT * FROM [test]
)
select
 a.A, b.B, isnull(c.C, 0)
from
 (select distinct A from e) a inner join
 (values ('B1'), ('B2'), ('B3'), ('B4')) b(B) on 1=1) left join
 e c on c.A = a.A and c.B = b.B;

Но учтите, CTE - это просто "синтаксический сахар". Фактически запрос из CTE будет выполнен столько раз, сколько он упомянут в последующих CTE и основном запросе.
30 янв 17, 19:08    [20163384]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
traregs
Member

Откуда:
Сообщений: 15
invm
Но учтите, CTE - это просто "синтаксический сахар". Фактически запрос из CTE будет выполнен столько раз, сколько он упомянут в последующих CTE и основном запросе.

Если я правильно понимаю, единственной альтернативой многочисленным перерасчётам, будут - временные таблицы?
30 янв 17, 19:21    [20163442]     Ответить | Цитировать Сообщить модератору
 Re: Создать полную структуру  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
traregs
Если я правильно понимаю, единственной альтернативой многочисленным перерасчётам, будут - временные таблицы?
Да. Но это не обязательно всегда выгоднее.
30 янв 17, 19:25    [20163464]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить