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

#mysql #sql

Вопрос:

Мои таблицы выглядят следующим образом:

 CREATE TABLE ingredient_tbl
(
ingredient_id VARCHAR (3),
ingredient_name VARCHAR (30) NOT NULL,
ingredient_decription VARCHAR (50) NOT NULL,
CONSTRAINT ingredient_id_pk PRIMARY KEY (ingredient_id)
);

CREATE TABLE allergen_ingredient_link_tbl /* One ingredient contains 0 to many allergen. One allergen is in 1 to many ingredient. */
(
ingredient_id VARCHAR (3),
allergen_id VARCHAR (3),
FOREIGN KEY (allergen_id) REFERENCES allergen_tbl(allergen_id),
FOREIGN KEY (ingredient_id) REFERENCES ingredient_tbl(ingredient_id),
PRIMARY KEY (ingredient_id, allergen_id)
);

Пока что мой sql-запрос, который подсчитывает количество аллергенов и упорядочивает список:

 SELECT ingredient_name, COUNT(link.allergen_id) "Number of Allergens"
FROM ingredient_tbl i
INNER JOIN allergen_ingredient_link_tbl link
    ON link.ingredient_id = i.ingredient_id
GROUP BY ingredient_name
ORDER BY "Number of Allergens" DESC;

я пробовал это, но он возвращает только максимальное значение. Я не могу понять, как вернуть названия ингредиентов:

 SELECT MAX("Number of Allergens")
    FROM
        (SELECT ingredient_name, COUNT(link.allergen_id) "Number of Allergens"
        FROM ingredient_tbl i
        INNER JOIN allergen_ingredient_link_tbl link
            ON link.ingredient_id = i.ingredient_id
        GROUP BY ingredient_name
        ORDER BY "Number of Allergens" DESC);

1. Используйте свой предыдущий запрос (тот, в котором нет MAX и используется LIMIT 1 только для извлечения первой строки.

2. что select version(); дает?

3. VARCHAR(3) — странный выбор для идентификатора

4. Почему VARCHAR(3) является странным выбором для идентификатора?

Ответ №1:

Вы можете использовать order by и limit :

 SELECT ingredient_name, COUNT(link.allergen_id) "Number of Allergens"
FROM ingredient_tbl i JOIN
    Iallergen_ingredient_link_tbl link
    ON link.ingredient_id = i.ingredient_id
GROUP BY ingredient_name
ORDER BY "Number of Allergens" DESC
LIMIT 1;

Примечание: это возвращает только один ингредиент с максимальным количеством. Если вам нужны связи, вы можете использовать оконные функции.

Редактировать:

Если вы хотите обрабатывать связи, используйте RANK() :

 SELECT i.*
FROM (SELECT ingredient_name, COUNT(*) "Number of Allergens",
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM ingredient_tbl i JOIN
          Iallergen_ingredient_link_tbl link
          ON link.ingredient_id = i.ingredient_id
      GROUP BY ingredient_name
     ) i
WHERE seqnum = 1;

1. Что делать, если более одного ингредиента имеют максимальное значение аллергенов? Кроме того, как я мог бы вывести это в виде инструкции, например, «Ингредиент (ы) с наибольшим количеством аллергенов есть / есть» ingredient_name (ы)?

Ответ №2:

Спасибо за всю вашу помощь. Мой последний рабочий запрос:

 SELECT 'Ingredient(s) with most allergens are: ' || ingredient_name || ', with ' || MAX("Number_of_Allergens") || ' each.' 
FROM (
    SELECT ingredient_name, COUNT(link.allergen_id) "Number_of_Allergens",
        RANK() OVER (ORDER BY COUNT(link.allergen_id) DESC) as rank_num
    FROM ingredient_tbl i 
    JOIN allergen_ingredient_link_tbl link
        ON link.ingredient_id = i.ingredient_id
    GROUP BY ingredient_name
     ) 
WHERE rank_num = 1
GROUP BY ingredient_name;