Как правильно сделать SQL запрос по нескольким значениям одного поля?

Question

[Karen Kratyan]

Пример таблица:

| product_id | field_id | value_id |
|     1      |  f1      |     v1   |
|     2      |  f1      |     v2   |
|     3      |  f2      |     v3   |
|     4      |  f2      |     v4   |

Получить product_id при условии, что value_id in (v1, v2) AND value_id in (v3, v4)
Через group by product_id having COUNT(*) = 2 думаю не получится, т.к. могут быть совпадения значений у одного поля(например, у товара с field_id = f2 и value_id in (v3, v4) ). Хотя если грамотно составить, то, возможно, получится. У меня не получилось :)

Comments

[alexalexes]

value_id in (v1, v2) AND value_id in (v3, v4)

У вас условие and взаимоисключает выбор строки.
Сформулируйте корректно условия того, что хотите получить, и возможно вопрос отпадет сам собой.

[Karen Kratyan]

alexalexes, Есть таблица, в котором хранятся связи полей(f1, f2) со значениями(v1-v4) для товаров(id). Нужно получиться id товаров, для которых у поля f1 должно быть одно из значений v1, v2. А также, чтобы у поля f2, так же были одно из значений v3, v4. В общем и целом ясно?)

Answer 1

[alexalexes]

Не знаю, как это правильно называется. "Межстрочный AND по значениям столбца" реализуется пересечением одной таблицы самой себя столько раз, сколько нужно членов and для сравнения.
В общем, в выражении exists реализована такая конструкция.

select ext.*
from ext
where 
exists (select 1 from t as t1
                         join t as t2 on t1.field_id = t2.field_id
                          where t1.field_id = ext.f1
                              and t1.value_id = v1 
                              and t2.value_id = v2 )
and exists (select 1 from t as t1
                         join t as t2 on t1.field_id = t2.field_id
                          where t1.field_id = ext.f2
                              and t1.value_id = v3 
                              and t2.value_id = v4 )

Comments

[Karen Kratyan]

Спасибо. Буду пробовать. Главное, чтоб правильно работало и быстро

Answer 2

[Руслан .]

Можно вот так попробовать:

SELECT product_id, 
        SUM(CASE WHEN field_id = 'f1' AND value_id in (v1, v2) THEN 1 ELSE 0 END), 
        SUM(CASE WHEN field_id = 'f2' AND value_id in (v3, v4) THEN 1 ELSE 0 END)
   FROM product
  WHERE value_id In (v1, v2, v3, v4) 
  GROUP BY product_id
 HAVING SUM(CASE WHEN field_id = 'f1' AND value_id in (v1, v2) THEN 1 ELSE 0 END) = 1
    AND SUM(CASE WHEN field_id = 'f2' AND value_id in (v3, v4) THEN 1 ELSE 0 END) = 1

Comments

[Karen Kratyan]

Спасибо. Но, мне кажется, код ниже будет быстрее работать. Не?

[Руслан .]

Karen Kratyan, сложно оценивать. Нужно взять таблицу с большим объёмом данных и смотреть, кроме этого ещё от индексов зависит. Возможно, что разницы вообще не увидите.

[Максим]

Руслан ., Как решение с константами оно подходит, но если пары значений как данные из другой таблицы описывать 'f1' и 'f2' может не быть возможности.

[Руслан .]

Максим, если я правильно понял задачу, то можно ту вторую таблицу соединить с текущей и в условиях запроса проверять значения уже из новой таблицы. При наличии правильных индексов на новой таблице должно работать нормально.

Answer 3

[Максим]

Я не очень понял что Вы хотели и чего нельзя с having count, но помоему это выглядит так:

SELECT product_id, count(distinct field_id) cnt_f, count(distinct value_id) cnt_v
from product
where (   (field_id =  'f1' and value_id in ('v1', 'v2')) 
          or  (field_id =  'f2' and value_id in ('v3', 'v4'))
           )
group by  product_id
having count(distinct field_id) = 2 and count(distinct value_id) = 2

Если надо чтобы уникальных значений было по 2, если Вы боитель за дубликаты можете ввести вместо count(distinct value_id) -> count(*) = 2

Comments

[Karen Kratyan]

Спасибо.
Вот distinct в count я забыл: count(distinct field_id)
А оптимизировать where, если так:

value_id in ('v1', 'v2', 'v3', 'v4') AND field_id in ('f1', 'f2')

Логический должно быть тоже самое? В моем случае (field_id in ('f1', 'f2')) можно вообще отбросить. Буду пробовать

[Максим]

Karen Kratyan, я условие составлял ... просто так, думал у Вас поля других таблиц
field_id in ('f1', 'f2') пар в 2 раза больше чем в моем примере, но данные ваши и Вам решать ...
Я просто показал что можно контролить кол-во данных таким способом как count(distinct field).
Если подошло, оцените как решение. ;-)