Извлечь строку из таблицы, которая соответствует значению в одном столбце и наличию подстроки в строке через запятую в другом столбце

Question

Вопрос Как найти строки, которые соответствуют значению в одном столбце (с помощью оператора =), а в другом столбце должна быть подстрока (с помощью find_in_set или другого совпадения подстроки).

Сценарий: у меня есть три таблицы MySQL:

1. Фигуры. В них есть детали таких фигур, как идентификатор, имя, создатель, созданный, измененный, статус и т. Д. Для удобства я упомянул только два столбца:

 id |  name 
 1  | red green yellow circle
 2  | red square in yellow circle
 3  | 3D yellow red trapezium

2. Атрибуты: хранит различные атрибуты со всеми возможными значениями, разделенными запятыми для каждого атрибута.

id  |  term     | value
 1  |  shape    | circle,square,rectangle,parallelogram,trapezium
 2  |  color    | red,green,yellow,blue,white,orange
 3  | dimension | 1D,2D,3D

3. Сопоставление атрибутов фигуры: хранит сопоставление фигуры, атрибутов и только те значения, разделенные запятыми, которые применимы к этой конкретной комбинации фигура-атрибут.

id | figure_id | attribute_id | value
 1 | 1         | 1            | circle
 2 | 1         | 2            | red,green,yellow
 3 | 2         | 1            | circle,square
 4 | 2         | 2            | red,yellow
 5 | 3         | 1            | trapezium
 6 | 3         | 2            | yellow,red
 7 | 3         | 3            | 3D

Цель: я хочу написать запрос, который возвращает мне figure_id из таблицы figure_attribute_mapping для соответствующего значения атрибута в этой строке отображения атрибута-фигуры.

Случай I: поиск фигуры квадратной формы. Мой запрос:

Select figure_id from figure_attribute_mapping 
where (attribute_id = 1 AND find_in_set('square',value)<>0);

Ожидаемый ответ: 2 Результат: положительный

Случай II: поиск красного цвета фигуры. Мой запрос:

select figure_id from figure_attribute_mapping 
where (attribute_id = 2 AND find_in_set('red',value))

Ожидаемый ответ: 1, 2, 3 Результат: положительный

Случай III: поиск красной квадратной фигуры. Мой запрос:

select figure_id from figure_attribute_mapping 
where 
      (attribute_id = 1 AND find_in_set('square',value)<>0) 
  AND (attribute_id = 2 AND find_in_set('red',value)<>0)

Ожидаемый ответ: 2 Результат: отрицательный

Случай IV: поиск красного квадрата в желтом круге. Мой запрос:

 select figure_id from figure_attribute_mapping 
where
  ( 
       (attribute_id = 1 AND find_in_set('square',value)<>0) 
  AND  (attribute_id = 1 AND find_in_set('circle',value)<>0)
  ) 
AND
 ( 
       (attribute_id = 2 AND find_in_set('red',value) <>0) 
  AND (attribute_id = 2 AND find_in_set('yellow',value)<>0) 
 )

Ожидаемый ответ: 2 Результат: отрицательный

Я могу найти figure_id, когда тип атрибута похож, но не могу найти figure_id, когда возникает вопрос о нескольких атрибутах.

Может кто-нибудь, пожалуйста, помогите в создании запроса MySQL.

Answer 1

Одним из решений является группирование ваших строк по figure_id и поиск по атрибуту value соединяя это.

Ниже приведены запросы для каждого из ваших случаев:

Случай I:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('square', z.merged_value);

Выход

id     | figure_id | merged_value
+------+-----------+--------------------------+
|    3 |         2 | circle,square,red,yellow

Случай II:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('red', z.merged_value);

Выход

| id   | figure_id | merged_value             |
+------+-----------+--------------------------+
|    1 |         1 | circle,red,green,yellow  |
|    3 |         2 | circle,square,red,yellow |
|    5 |         3 | trapezium,yellow,red,3D  |

Случай III:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('square', z.merged_value)
AND FIND_IN_SET('red', z.merged_value);

| id   | figure_id | merged_value             |
+------+-----------+--------------------------+
|    3 |         2 | circle,square,red,yellow |

Случай IV:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('square', z.merged_value)
AND FIND_IN_SET('circle', z.merged_value)
AND FIND_IN_SET('red', z.merged_value)
AND FIND_IN_SET('yellow', z.merged_value);

Выход

| id   | figure_id | merged_value             |
+------+-----------+--------------------------+
|    3 |         2 | circle,square,red,yellow |