Помогите написать SQL запрос на вставку данных

Question

СУБД: MySQL 8.0.29

Есть три таблицы:

CREATE TABLE userBonuses (
    id INT,
    userId INT,
    bonusId INT
)

CREATE TABLE orders (
    id INT,
    userId INT
)

CREATE TABLE bonuses (
    id INT,
    ordersCount INT
)

Необходимо выбрать все из таблицы bonuses. Выбрать ордеры, где поле bonuses.ordersCount больше чем количество ордеров на пользователя (COUNT(*) AS order_counts) и GROUP BY userId.

После чего, необходимо на каждого пользователя создать бонусы в таблице userBonuses.

Например, пользователь с ID 10 имеет 3 ордера.

INSERT INTO
    `orders` (`id`, `userId`)
VALUES
    (1, 10),
    (2, 10),
    (3, 10)

Правила для начисления бонусов:

INSERT INTO
    `bonuses` (`id`, `ordersCount`)
VALUES
    (1, 1),
    (2, 2),
    (3, 6)

По правилу с ID 1, пользователь должен выполнить 1 ордер, чтобы создать запись в бонус. У нас, пользователь с ID 10 выполнил 3 ордера. На это правило, и на этого пользователя должно быть создано 3 бонуса.
По правилу с ID 2, пользователь должен выполнить 2 ордера, пользователь с ID 10 получит 1 бонус на это правило.
По правилу с ID 3, пользователь должен выполнить 6 ордеров, пользователь с ID 10 не получит бонусов за это.

Попытался написать запрос для выборки. Но вот сделать начисление бонусов не могу. Вот сам запрос. Реализует только выборку ордеров.

SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            `bonuses` AS `bonuses`
    ) AS `bp`
    JOIN (
        SELECT
            `orders`.`userId`,
            COUNT(*) AS `order_counts`
        FROM
            `orders`
        GROUP BY
            `userId`
    ) AS `or` ON `or`.`order_counts` > `bp`.`ordersCount`

Пример запросов в конструкторе.

Answer 1

По отдельности:

WITH 
orders_amount AS (
    SELECT userId, COUNT(*) ordersCount
    FROM orders
    GROUP BY 1
)
SELECT orders_amount.userId,
       orders_amount.ordersCount,
       bonuses.id bonusId,
       FLOOR(orders_amount.ordersCount / bonuses.ordersCount) bonusesCount
FROM orders_amount
JOIN bonuses ON orders_amount.ordersCount >= bonuses.ordersCount

и суммарно:

WITH 
orders_amount AS (
    SELECT userId, COUNT(*) ordersCount
    FROM orders
    GROUP BY 1
)
SELECT userId, SUM(FLOOR(orders_amount.ordersCount / bonuses.ordersCount)) bonusesCount
FROM orders_amount
JOIN bonuses ON orders_amount.ordersCount >= bonuses.ordersCount
GROUP BY 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=44891d8c8522ec1d7127e9e3e00a80b0

Для вставки в третью таблицу добавь INSERT и убери лишние поля из списка вывода.

БЛОГ НА HUSL

Помогите написать SQL запрос на вставку данных

Ответы (1 шт):