#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
У меня есть список цветов (красный, зеленый, синий), список размеров (S, M, L) и некоторые входные данные, которые поступают следующим образом:
| item | color | size | price |
--------------------------------
| 1 | red | S | $10 |
| 1 | other | S | $8 |
| 2 | all | all | $20 |
| 3 | green | S | $14 |
| 3 | green | other | $16 |
Что я хочу сделать, так это расширить строки «другие» и «все», чтобы в итоге у меня была одна строка для каждого цвета и размера:
| item | color | size | price |
--------------------------------
| 1 | red | S | $10 |
| 1 | green | S | $8 |
| 1 | blue | S | $8 |
| 2 | red | S | $20 |
| 2 | red | M | $20 |
| 2 | red | L | $20 |
| 2 | green | S | $20 |
| 2 | green | M | $20 |
| 2 | green | L | $20 |
| 2 | blue | S | $20 |
| 2 | blue | M | $20 |
| 2 | blue | L | $20 |
| 3 | green | S | $14 |
| 3 | green | M | $16 |
| 3 | green | L | $16 |
Приведенный ниже код дает мне желаемые результаты для столбца цвета:
Создать сценарий:
DECLARE @inputs TABLE
(
item int
,color varchar (10)
,size varchar (10)
,price float
);
insert into @inputs
values
(1, 'red', 'S', 10),
(1, 'other', 'S', 8),
(2, 'all', 'all', 20),
(3, 'green', 'S', 14),
(3, 'green', 'other', 16);
DECLARE @colors TABLE
(
color varchar (10)
);
insert into @colors
values
('red'),
('green'),
('blue');
DECLARE @sizes TABLE
(
size varchar (10)
);
insert into @sizes
values
('S'),
('M'),
('L');
оператор select:
/* select rows with specific colors */
SELECT i.item
,i.color
,i.size
,i.price
FROM @inputs i
WHERE EXISTS (SELECT 1
FROM @colors c
WHERE i.color = c.color
)
--
UNION ALL
--
/* select rows for all other colors */
SELECT i.item
,c.color
,i.size
,i.price
FROM @inputs i
CROSS JOIN @colors c
WHERE i.color IN ('all', 'other')
AND NOT EXISTS (SELECT 1
FROM @inputs i2
WHERE c.color = i2.color
AND i.item = i2.item
AND i.size = i2.size
)
Но я изо всех сил пытаюсь применить ту же логику к столбцу size, не повторяя приведенный выше код (поскольку мне нужно объединить «фактический размер» и «все / другие» части на основе результатов). Каков наилучший способ подойти к этому?
Комментарии:
1. В вашем коде вы упоминаете, что other и all должны быть переведены во все возможные категории в этом столбце, однако для строки «3, зеленый, другое, 16» существует только расширение для размера M и L. Это ошибка?
2. Не ошибка, «другое» должно быть переведено как «все, кроме того, что было указано». Для «3, зеленый, другое, 16», поскольку существует «3, зеленый, S, 14», другое будет означать красный и синий.
3. Итак, чтобы подтвердить — другое зависит от товара и цвета, но не от цены?
Ответ №1:
Вы должны быть в состоянии получить искомый результат с помощью OUTER APPLY предложения. Следующий пример демонстрирует:
DECLARE @inputs TABLE
(
item int
,color varchar (10)
,size varchar (10)
,price float
);
insert into @inputs
values
(1, 'red', 'S', 10),
(1, 'other', 'S', 8),
(2, 'all', 'all', 20),
(3, 'green', 'S', 14),
(3, 'green', 'other', 16);
DECLARE @colors TABLE
(
color varchar (10)
);
insert into @colors
values
('red'),
('green'),
('blue');
DECLARE @sizes TABLE
(
size varchar (10)
);
insert into @sizes
values
('S'),
('M'),
('L');
SELECT I.item
,(
CASE I.color
WHEN 'all' THEN CA.color
WHEN 'other' THEN CA.color
ELSE I.color
END
) AS color
,(
CASE I.size
WHEN 'all' THEN SA.size
WHEN 'other' THEN SA.size
ELSE I.size
END
) AS size
,price
FROM @inputs I
OUTER APPLY (
SELECT color
FROM @colors colors
WHERE (I.color = 'all' OR I.color = 'other') AND colors.color NOT IN (SELECT color from @inputs where item = I.item)
) CA
OUTER APPLY (
SELECT size
FROM @sizes sizes
WHERE (I.size = 'all' OR I.size = 'other') AND sizes.size NOT IN (SELECT size from @inputs where item = I.item)
) SA
Ответ №2:
Как я уже прокомментировал, в ваших образцах данных, вероятно, ошибка. Однако это должно быть простым решением для вас:
select a.item
,case when a.color in ('all','other') then b.color
,case when a.size a.size in ('all','other') then b.size
,a.price from inputs a
left join colors b on a.color in ('all','other')
left join sizes b on a.size in ('all','other')
Комментарии:
1. Не совсем уверен, что делает код, выполнение вашего запроса по входным данным дает мне 9 строк назад для элемента 2 и всех перестановок цвета и размера…
2. Ах, извините, должны были быть оставленные соединения. Однако то, что он делает, это то, что он переставляет все возможности, когда находит «другое или «все»