Товары только с одним доступным цветом на складе - SQL запрос

Question

У меня есть таблица, в которой перечислены тысячи продуктов. Продукт может быть стандартным (123450.000.000), одного цвета (123456.BLA.000), одного размера (123456.000.LAR) или одновременно цветом и размером (123456.BLA.LAR).

Продукт может иметь несколько цветов (123456.BLA.000, 123456.YEL.OOO и т. Д. И т. Д.). Я пытаюсь сделать запрос, который возвращает продукт, который имеет несколько цветов, но только один цвет в наличии, т.е. ProductQTY = 1, а остальные цвета отсутствуют на складе.

Все, что я смог придумать, это запрос ниже, но это просто возвращает все "альтернативные" продукты, которые имеют 1 на складе. Что мне нужно добавить или изменить, чтобы получить результаты, когда продукт имеет разные цвета, но только ОДИН из этих цветов есть в наличии, а остальные нет в наличии? Нужно ли делать СОЮЗ?

select *
from Product
where productcode NOT LIKE ('%000.000')
AND ProductQTY = '1'

Answer 1

Во-первых, это ужасная структура. Храните информацию о цвете и размере в отдельных таблицах, не делайте их частью одной массивной переменной.

Во-вторых, вы хотите сделать запрос, который выбирает идентификаторы товаров (1-6, похоже), которые имеют количество> 1 с присутствующим цветом, а сумма их количеств точно равна 1 (в соответствии с вашим вопросом - если эта запись может иметь кол-во> 1 и все в порядке, это немного сложнее).

select * from product where substr(product,1,6) in (
select substr(product,1,6) from product 
where not (product like '%.000.%')
group by 1
having count(1) > 1
and sum(ProductQTY)=1
)

Answer 2

Вопрос был бы намного проще, если бы вы разделили свойства в разных столбцах. Но вы можете сделать это и в запросе:

select code from 
  (select substring(productcode,-3) as size,
   substring(productcode,-7,3) as colour,
   substring(productcode,0,len(prodcutcode)-7) as code
  from product) t
group by code
having sum(productQTY) = 1 

//or count(*) = 1 to get all unique ones