Mysql выберите группу запросов concat

У меня есть запрос на выборку, и я не могу заставить его работать должным образом. Я надеялся на несколько предложений от более продвинутых разработчиков MySQL. Итак, мои таблицы:

 CREATE TABLE IF NOT EXISTS `gv` ( 
`id` int(11) NOT NULL AUTO_INCREMENT,
`option_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `gv` (`id`, `option_id`, `group_id`) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 2),
(4, 4, 3),
(5, 5, 4),
(6, 6, 4); 

CREATE TABLE IF NOT EXISTS `igv` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `gv_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `igv` (`id`, `item_id`, `gv_id`) VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 1),
(4, 2, 2),
(6, 3, 5),
(7, 4, 2),
(8, 2, 6); 

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `items` (`item_id`) VALUES
(1),
(2),
(3),
(4),
(5);

Теперь я объясню, что делают эти таблицы:

  1. gv происходит из group_values. Группа - это как свойство для предмета. Пример: цвет.

    • каждая строка содержит значения для группы. Пример: красный (option_id) для группы Color (group_id).

  2. igv исходит из значений группы товаров. Предмет - это основная сущность в моем проекте, как продукт.

    • каждая строка содержит отношения между элементами и group_values. Пример: предмет с красным цветом.
    • он связан с таблицей gv с помощью gv_id (igv.gv_id = gv.id).

  3. items приходит из items:) Для этого примера я упростил только один столбец item_id.

Что мне нужно для запроса:

Ну, в моем приложении у меня есть выбранный элемент ( my_item) (с 0 или более присоединенными значениями группы). Мне нужны все элементы с точно такими же определенными значениями group_value, или для групп my_item не указаны значения. В php я могу извлечь все присоединенные группы в виде массива с идентификаторами, а также все group_values. Но я не нашел способа выбрать то, что мне нужно, из этой базы данных. Я буду очень признателен за любой вклад в этот огромный трудоемкий выбор.

Спасибо!

Краткий набросок. Слева у нас есть элемент, который соответствует всем нужным элементам (через запятую).

Источник

2 ответа

Решение

Итак, для начала нам нужно найти все элементы с группой, соответствующей другому элементу:

SELECT igv.item_id, sigv.item_id FROM igv
  INNER JOIN gv ON igv.gv_id = gv.id
  INNER JOIN gv sgv ON gv.group_id = sgv.group_id
  INNER JOIN igv sigv ON sigv.gv_id = sgv.id
  WHERE igv.item_id = items.item_id
    AND sigv.item_id = similar.item_id

Нас тогда интересует только когда опция не совпадает:

SELECT igv.item_id, sigv.item_id dissimilar FROM igv
  INNER JOIN gv ON igv.gv_id = gv.id
  INNER JOIN gv sgv ON gv.group_id = sgv.group_id
  INNER JOIN igv sigv ON sigv.gv_id = sgv.id
  WHERE igv.item_id = items.item_id
    AND sigv.item_id = similar.item_id
    AND gv.option_id != sgv.option_id

Тогда нас интересуют только те предметы, которые не выбраны выше. Это можно сделать используя NOT EXISTS или LEFT JOIN, Вот как это сделать с NOT EXISTS:

SELECT items.item_id, similar.item_id similar_id
  FROM items similar
  INNER JOIN items
  WHERE items.item_id != similar.item_id
    AND NOT EXISTS (
    SELECT igv.item_id, sigv.item_id dissimilar FROM igv
      INNER JOIN gv ON igv.gv_id = gv.id
      INNER JOIN gv sgv ON gv.group_id = sgv.group_id
      INNER JOIN igv sigv ON sigv.gv_id = sgv.id
      WHERE sigv.item_id = similar.item_id
        AND igv.item_id = items.item_id
        AND gv.option_id != sgv.option_id
    )

Результат:

ITEM_ID SIMILAR_ID
1       3
1       5
2       4
2       5
3       1
3       4
3       5
4       2
4       3
4       5
5       1
5       2
5       3
5       4
Источник

Обновить:

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

id_provided | result
--------------------
1             2 & 5
2             1 & 4 & 5
3             5
4             2 & 5
5             -

Если это правильно, следующий запрос дает желаемый результат:

SELECT 
COALESCE(i2.item_id, i.item_id)
FROM
items i
LEFT JOIN igv ON igv.item_id = i.item_id
LEFT JOIN gv ON igv.gv_id = gv.id
LEFT JOIN gv gv2 ON gv.group_id = gv2.group_id AND gv.option_id = gv2.option_id
LEFT JOIN igv igv2 ON gv2.id = igv2.gv_id
LEFT JOIN items i2 ON igv2.item_id = i2.item_id
WHERE i.item_id = $yourItemId
AND i2.item_id != i.item_id OR i2.item_id IS NULL



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

SELECT DISTINCT
i.item_id
FROM
items i
LEFT JOIN igv ON igv.item_id = i.item_id
LEFT JOIN gv ON igv.gv_id = gv.id
GROUP BY i.item_id
HAVING 
GROUP_CONCAT(CONCAT(COALESCE(gv.option_id, 0), COALESCE(gv.group_id, 0)) ORDER BY gv.option_id, gv.group_id SEPARATOR ':')
=
(
SELECT
GROUP_CONCAT(CONCAT(COALESCE(sq_gv.option_id, 0), COALESCE(sq_gv.group_id, 0)) ORDER BY sq_gv.option_id, sq_gv.group_id SEPARATOR ':')
FROM
items sq_i
LEFT JOIN igv sq_igv ON sq_igv.item_id = sq_i.item_id
LEFT JOIN gv sq_gv ON sq_igv.gv_id = sq_gv.id
WHERE sq_i.item_id = $yourItemId /*insert the item_id here*/
GROUP BY sq_i.item_id
)

Источник
Другие вопросы по тегам