Nano Hash - криптовалюты, майнинг, программирование

MySQL — GROUP_CONCAT() с объединенной таблицей — неожиданные результаты

У меня есть две таблицы: одна описывает предметы, которые вы можете купить, а другая хранит «необходимые предметы» для покупки других предметов. В первой таблице есть некоторые предметы, которые требуют других предметов в качестве формы торговли. А иногда нужно определенное количество требуемых предметов. Вот схема двух таблиц:

+---------------+-----------------+------+-----+---------+----------------+
| Field         | Type            | Null | Key | Default | Extra          |
+---------------+-----------------+------+-----+---------+----------------+
| storeID       | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| itemID        | int(10)         | NO   |     | NULL    |                |
| quantity      | int(10)         | NO   |     | NULL    |                |
| cost          | int(10)         | NO   |     | NULL    |                |
+---------------+-----------------+------+-----+---------+----------------+

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| parentID | int(10) | NO   |     | NULL    |       |
| itemID   | int(10) | NO   |     | NULL    |       |
| quantity | int(10) | NO   |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

Вторая таблица связана с первой storeID <-> parentID. предметы в магазине могут иметь много необходимых предметов.

Проблема: мне нужно выполнить запрос, который возвращает все элементы в магазине, но ТАКЖЕ показывает все необходимые элементы и их количество. Это должно выглядеть примерно так:

4 x Item1,5 x Item2

У меня есть этот текущий запрос:

SELECT a.*, b.typeName, GROUP_CONCAT(CONCAT(c.quantity, " x ", d.typeName)) as `reqItems`
        FROM lpStore a 
        INNER JOIN typeNames b ON (a.typeID = b.typeID)
        INNER JOIN lpRequiredItems c ON (a.storeID = c.parentID)
        INNER JOIN typeNames ON (d.typeID = c.typeID)
        GROUP BY c.parentID
        ORDER BY a.cost ASC

typeNames — это просто таблица, которая связывает идентификаторы элементов с их фактическими именами.

Теперь этот запрос работает хорошо, однако кажется, что он не включает элементы, которые НЕ ИМЕЮТ ОБЯЗАТЕЛЬНЫХ ЭЛЕМЕНТОВ. Я подозреваю, что это одно из JOINS вместе с GROUP BY (может быть, группирует все значения NULL, которые не существуют в этой таблице?), Однако я еще не смог понять это. Мне нужно, чтобы в этом столбце возвращалось пустое значение или NULL, если ничего не требуется.

РЕДАКТИРОВАТЬ: Пример данных

Я удалил объединение таблиц с таблицей имен элементов для простоты.

Примеры данных доступны здесь: http://www.sqlfiddle.com/#!2/d8dca/ 1

Фактические результаты:

+---------+--------+------+----------+-----------------------------+
| storeID | typeID | cost | quantity | reqItems                    |
+---------+--------+------+----------+-----------------------------+
|       1 |   2514 | 2000 |        3 | 5 x 3668,10 x 4825          |
|       3 |   8785 | 5000 |        2 | 1 x 4875,15 x 1102,5 x 9813 |
|       4 |    579 | 1500 |        5 | NULL                        |
+---------+--------+------+----------+-----------------------------+

Ожидаемые результаты:

+---------+--------+------+----------+-----------------------------+
| storeID | typeID | cost | quantity | reqItems                    |
+---------+--------+------+----------+-----------------------------+
|       1 |   2514 | 2000 |        3 | 5 x 3668,10 x 4825          |
|       2 |   3199 | 1000 |        1 | NULL                        |
|       3 |   8785 | 5000 |        2 | 1 x 4875,15 x 1102,5 x 9813 |
|       4 |    579 | 1500 |        5 | NULL                        |
+---------+--------+------+----------+-----------------------------+
mysql sql join
28.01.2013

  • Можете ли вы дать образцы данных с желаемым результатом? 28.01.2013
  • Сделанный. Доступны образцы данных 28.01.2013

Ответы:


1

Проблема заключается в том, что GROUP BY parentId поместите GROUP_CONCAT() в подзапрос, а затем используйте LEFT JOIN в подзапросе. Когда GROUP BY parentId равно null, эти значения игнорируются, поэтому вы не возвращаете результат:

SELECT  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity` , 
        `reqItems` 
FROM    lpStore a
LEFT JOIN
(
  select  parentID,
     GROUP_CONCAT( CONCAT( quantity,  " x ", typeID ) ) AS  `reqItems` 
  from lpRequiredItems
  group by parentID
) c 
  ON ( a.storeID = c.parentID ) 
ORDER   BY  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity`
LIMIT 0 , 30

См. SQL Fiddle с демонстрацией.

Результат:

| STOREID | TYPEID | COST | QUANTITY |                    REQITEMS |
--------------------------------------------------------------------
|       1 |   2514 | 2000 |        3 |          5 x 3668,10 x 4825 |
|       2 |   3199 | 1000 |        1 |                      (null) |
|       3 |   8785 | 5000 |        2 | 5 x 9813,1 x 4875,15 x 1102 |
|       4 |    579 | 1500 |        5 |                      (null) |
28.01.2013
  • Спасибо! Я думал, что это может быть 28.01.2013
  • Дружище, спасибо, эта проблема ломала мне голову часа 2, без твоего поста я бы не нашел решения. 04.09.2014

    • 2

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

    SELECT a.*, b.typeName, GROUP_CONCAT(CONCAT(c.quantity, " x ", d.typeName)) as `reqItems`
        FROM lpStore a 
        INNER JOIN typeNames b ON (a.typeID = b.typeID)
        LEFT JOIN lpRequiredItems c ON (a.storeID = c.parentID)
        INNER JOIN typeNames ON (d.typeID = c.typeID)
        GROUP BY c.parentID
        ORDER BY a.cost ASC
    28.01.2013
  • Пробовал, не работает. Я использовал LEFT, RIGHT, INNER и т. д., и я не могу заставить его работать. 28.01.2013

    • 3

    Вам просто нужно использовать LEFT JOIN вместо INNER JOIN

    SELECT  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity` , 
        GROUP_CONCAT( CONCAT( c.quantity,  " x ", c.typeID ) ) AS  `reqItems` 
FROM    lpStore a
        LEFT JOIN lpRequiredItems c 
            ON ( a.storeID = c.parentID ) 
GROUP   BY c.parentID
ORDER   BY  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity` 
LIMIT 0 , 30
    28.01.2013
  • Я обновил образцы данных. Обновление добавляет еще один элемент с 0 обязательными элементами. Я ожидаю, что он вернет 4 результата, причем 2 из них будут показывать null в столбце reqItems. Однако он показывает только 1 нулевой результат. Я предполагаю, что это из-за GROUP BY c.parentID, и он группирует все элементы без reqItems в 1 строку. 28.01.2013
    • Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..