Как выполнить SELECT с таким условием?

Question

[aopil]

Есть таблица orders в ней есть список заказов и SERVICE_ID.
Есть таблица services в ней есть список услуг и SERVICE_LIMIT.

Для простоты понимания пример:
В таблице services есть записи

SERVICE_ID | SERVICE_NAME | SERVICE_LIMIT
5 | Sample Service      | 10
8 | Other tool          | 5

В таблице orders есть записи:

ID | SERVICE_ID | STATUS
1  |  5     | 0
2  |  5     | 0
3  |  5     | 0
4  |  8     | 0
5  |  8     | 0
6  |  8     | 0
7  |  8     | 0

Из таблицы orders мне нужно получить заказы, но только в том количестве, которое указано в таблицы services в колонке SERVICE_LIMIT.

т.е. для Sample Service не более 10 шт, а для Other tool не более 5 шт.

Comments

[Akina]

Какие именно? по какому критерию отбирать те, что вернуть, и отсеивать те, то не возвращать?
И указывайте точную версию СУБД.

[aopil]

Akina, вывести все orders, но в количестве не превышающем SERVICE_LIMIT указанный для SERVICE_ID в таблице services

[Akina]

aopil, Вы не ответили на вопрос, Вы только повторили условие задания - но я его уже читал.

Я прошу критерий отбора. Есть десять записей, соответствующих условию. Надо только пять. Как отобрать эти пять из десяти? "Любые пять" - не ответ. Требуется критерий, по которому любой отберёт совершенно одни и те же пять из десяти.

[aopil]

Akina, если у нас лимит 5, то даже если 2000 записей подходят под наши условия, то мы должны взять первые 5, а остальные не трогать. Что значит критерий отбора? Критерий это и есть условие.

[Akina]

мы должны взять первые 5

Уже лучше, уже хоть какой-то критерий,
Как определяется, какие первые, какие последние? При какой сортировке они первые?

[aopil]

Akina, ORDER BY ID DESC;

Answer 1

[Akina]

aopil,

а для более старых версий невозможно выполнить то, что я хочу?

SELECT *
FROM services
JOIN (
    SELECT *,
           @row_number := CASE WHEN service_id = @service_id
                               THEN @row_number + 1
                               ELSE 1
                               END rownumber,
           @service_id := service_id
    FROM orders
    CROSS JOIN (SELECT @service_id := 0, @row_number := 0) init_vars
    ORDER BY service_id, id DESC
    ) enumerated_orders USING (service_id)
WHERE enumerated_orders.rownumber <= services.service_limit

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=1f2a95...

Comments

[Akina]

vinni65, Обычный JOIN с точки зрения стандартного SQL обязан сопровождаться ON clause. Тогда как CROSS JOIN, наоборот, наличия ON не допускает

Так что это не более чем обычная аккуратность.

Answer 2

[Rsa97]

Для MySQL 8.0 и выше

SELECT `o`.*
  FROM `services` AS `s`
  JOIN (
    SELECT `id`, `service_id`, `status`,
           ROW_NUMBER() OVER `win` AS `row_number`
      FROM `orders`
      WINDOW `win` AS (PARTITION BY `service_id`)
  ) AS `o` ON `o`.`service_id` = `s`.`service_id`
    AND `o`.`row_number` <= `s`.`service_limit`

Comments

[aopil]

Rsa97 а для более старых версий невозможно выполнить то, что я хочу?

[Rsa97]

aopil, Наверное, возможно. Только запрос будет сильно извратным и медленным.

[Akina]

aopil,

а для более старых версий невозможно выполнить то, что я хочу?

Да запросто. Только желательно знать статистику данных. В основном - какой в среднем процент записей будет отбираться для каждого service.

[aopil]

Akina, не более 100 записей для каждого service

[Akina]

aopil, это ничего не значит. Один тоже меньше ста. Я же чётко спросил - какой в среднем процент. Десять? двадцать? пятьдесят? ПРОЦЕНТОВ. А в ответ получаю какую-то незначащую фигню.