Имеется таблица предметов и их цветов

thing    color
------------
ручка    красный
ручка    синий
стул     красный
стул     синий
карандаш черный 
тетрадь  черный
тетрадь  синий

Назовем палитрой список цветов, которые мог принимать предмет
ручка — (красный, синий)
стул — (красный, синий)
карандащ — (черный)
тетрадь — (черный, синий)

Надо сгруппировать предметы по их палитре. В данном случае палитра (красный, синий) совпадает у ручки и стула. Они должны попасть в одну и ту же группу предметов. Остальные предметы друг с другом не пересекаются (попадают каждый в свою группу).

В итоге должно получиться две таблицы:

Палитры (входящие в них цвета)
palet color
-----------
1     красный
1     синий
2     черный
3     черный
3     синий 

Группировка предметов по палитрам
palet thing
------------
1     ручка
1     стул
2     карандаш
3     тетрадь

Помогите составить запросы для sqlite

Здравствуйте, Буравчик, Вы писали:

Диван синий и черный куда попадает?

Здравствуйте, Spinifex, Вы писали:

S>Здравствуйте, Буравчик, Вы писали:

S> Диван синий и черный куда попадает?

В одну группу с тетрадью, которая тоже синия и черная

Т.е. если на вход было подано (жирным выделены изменения относительно первого поста):

thing    color
------------
ручка    красный
ручка    синий
стул     красный
стул     синий
карандаш черный 
тетрадь  черный
тетрадь  синий
диван    синий
диван    черный

То получим:

Палитры (входящие в них цвета)
palet color
-----------
1     красный
1     синий
2     черный
3     черный
3     синий 

Группировка предметов по палитрам
palet thing
------------
1     ручка
1     стул
2     карандаш
3     тетрадь
3     диван

Здравствуйте, Буравчик, Вы писали:

Б>В одну группу с тетрадью, которая тоже синия и черная

Сорри, не заметил. Скажу прямо, sql lite я особо не знаю. Но вот что у меня получилось:
1. Инициализируем данные.

create table tbl ("thing" text(30), "color" text(30));
create table pallete
    ("id" integer primary key autoincrement not null, "pallete" text(30));
    
insert into tbl    ("thing", "color") values('pen', 'red');
insert into tbl ("thing", "color") values('pen', 'blue');
insert into tbl    ("thing", "color") values('chair', 'red');
insert into tbl    ("thing", "color") values('chair', 'blue');
insert into tbl    ("thing", "color") values('pencil', 'black');
insert into tbl    ("thing", "color") values('book', 'black');
insert into tbl    ("thing", "color") values('book', 'blue');

2. Сам запрос:

insert into pallete("pallete")
select p from
(
  select group_concat(b.color) as p
  from tbl b 
  group by b.thing
  order by b.color
)
group by p
;

select thngs.thing, p.id from (
  select b.thing, group_concat(b.color) as p
  from tbl b 
  group by b.thing
  order by b.color
) thngs
join pallete p on p.pallete = thngs.p
;

Здравствуйте, Spinifex, Вы писали:

S>Сорри, не заметил. Скажу прямо, sql lite я особо не знаю. Но вот что у меня получилось:

Спасибо!!! Принцип понятен.
Я не знал про функцию group_concat.

Здравствуйте, Буравчик, Вы писали:

Б>Я не знал про функцию group_concat.

Имей в виду, что это не стандартный SQL, хоть и поддерживается некоторыми СУБД (часто под разными именами). При большом количестве строк в группе можно наткнуться на внутренние ограничения на длину строки.

Стандартный SQL не богат операциями над множествами и, тем более, над множествами множеств.

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, Буравчик, Вы писали:

Б>>Я не знал про функцию group_concat.

W>Имей в виду, что это не стандартный SQL, хоть и поддерживается некоторыми СУБД (часто под разными именами). При большом количестве строк в группе можно наткнуться на внутренние ограничения на длину строки.

Учту на будущее. Но в данном случаем все ОК. Интересует SQLite, в котором group_concat имеется. И длина строки не должна получиться большой.

W>Стандартный SQL не богат операциями над множествами и, тем более, над множествами множеств.

Да уж. Я уже было подумал, что средствами SQL это не сделать.