Как составить сложный запрос?

Question

[lemonlimelike]

Всем привет! Имеется две таблицы Employee(сотрудники) и Appointment(клиенты)
Employee:

+-------------+------+------+-----+---------+----------------+
| Field       | Type | Null | Key | Default | Extra          |
+-------------+------+------+-----+---------+----------------+
| id          | int  | NO   | PRI | NULL    | auto_increment |
| service_id  | int  | YES  | MUL | NULL    |                |
| employee_id | int  | YES  |     | NULL    |                |
| priority    | int  | YES  |     | NULL    |                |
+-------------+------+------+-----+---------+----------------+

Appointment:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int      | NO   | PRI | NULL    | auto_increment |
| service_id  | int      | YES  | MUL | NULL    |                |
| employee_id | int      | YES  | MUL | NULL    |                |
| start_time  | time     | NO   |     | NULL    |                |
| end_time    | time     | NO   |     | NULL    |                |
| date        | date     | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

И нужно вывести список сотрудников у которых количество клиентов не больше 3 с учетом времени.
То есть, в таблице appointment такие данные:

+----+------------+-------------+-------------+--------+------------+----------+------------+
| id | service_id | employee_id | id_customer | start_time | end_time | date       |
+----+------------+-------------+-------------+--------+------------+----------+------------+
|  1 |          2 |           2 |           4 | 09:00:00   | 09:45:00 | 2020-12-31 |
|  2 |          2 |           3 |           4 | 09:00:00   | 09:45:00 | 2020-12-31 |
|  3 |          2 |           4 |           4 | 09:00:00   | 09:45:00 | 2020-12-31 |
|  4 |          1 |           1 |           4 | 09:00:00   | 09:45:00 | 2020-12-31 |
|  5 |          1 |           1 |           4 | 14:00:00   | 14:45:00 | 2021-01-20 |
+----+------------+-------------+-------------+--------+------------+----------+------------+

И такие данные в employee:

+----+------------+-------------+----------+
| id | service_id | employee_id | priority |
+----+------------+-------------+----------+
|  1 |          1 |           8 |        6 |
|  2 |          2 |           3 |        6 |
|  3 |          2 |           3 |        6 |
|  4 |          2 |           5 |        6 |

И значит мне нужно вывести список сотрудников, у которых клиенты записались на "09:00", и чтобы количество клиентов у сотрудника не превышало трех, типа having count(clients) <= 3
Как такой запрос написать?

Answer 1

[WStanley]

Непонятно почему у тебя в таблицу employee есть и id и employee_id, привожу запрос в котором appointment.employee_id = employee.id и позволил себе предположить что в таблице employee есть поле name
Есть нюанс, этот запрос покажет сотрудников у кот есть клиенты не более трех записанных именно на 09:00, если у этого же сотрудника есть еще два клиента(т.е. общее число клиентов 5) на 14:00 то он(сотрудник) тоже будет показан, но только с клиентами на 9:00 т.к. группировка есть по времени, надеюсь понятно написал

SELECT
    EMPLOYEE.ID
,   EMPLOYEE.NAME
FROM
    EMPLOYEE
LEFT OUTER JOIN
    APPOINTMENT
ON
    EMPLOYEE.ID =   APPOINTMENT.EMPLOYEE_ID
GROUP BY
    EMPLOYEE.ID
,   EMPLOYEE.NAME
,   APPOINTMENT.START_TIME
HAVING
    (COUNT(APPOINTMENT.EMPLOYEE_ID) <=  3)
AND (APPOINTMENT.START_TIME =   CONVERT(TIME, '09:00:00', 114))

Comments

[tukreb]

На работе тоже бесплатно работаете? :)

[WStanley]

tukreb, нет, работу мне оплачивают

[lemonlimelike]

Нет, Это не правильно. Нужно выбрать сотрудников у которых service_id = 1 и количество клиентов, в интервал времени с 11:00:00 до 12:00:00 должен быть меньше либо равен 3

[WStanley]

lemonlimelike, employee_id в таблице employee это ключ сам на себя? Я правильно понял?

[lemonlimelike]

WStanley, Неа, вы не правильно поняли. При создании записи в таблицу employee, в поле employee_id ставится id сотрудника, но этот id приходит из другого API.

[lemonlimelike]

WStanley, Но я уже составил запрос. Такой

SELECT COUNT(a.id) as total, e.* FROM employee AS e
LEFT JOIN appointment as a ON (
  a.employee_id = e.id AND (
    a.start_time >= "11:00:00" OR
    a.end_time <= "11:45:00"
  )
  AND a.service_id = 1
)
GROUP BY e.id
HAVING total < 3
ORDER BY total ASC

[WStanley]

lemonlimelike, только хотел добавить

SELECT
    EMPLOYEE.ID
,   EMPLOYEE.PRIORITY
,   EMPLOYEE.SERVICE_ID
FROM
    EMPLOYEE
LEFT OUTER JOIN
    APPOINTMENT
ON
    EMPLOYEE.ID =   APPOINTMENT.EMPLOYEE_ID
WHERE
    (
        EMPLOYEE.SERVICE_ID =   1
    )
GROUP BY
    EMPLOYEE.ID
,   APPOINTMENT.START_TIME
,   EMPLOYEE.PRIORITY
,   EMPLOYEE.SERVICE_ID
HAVING
    (
        APPOINTMENT.START_TIME  BETWEEN CONVERT(TIME, '11:00:00', 114)  AND CONVERT(TIME, '12:00:00', 114)
    )
AND (COUNT(APPOINTMENT.EMPLOYEE_ID) <=  3)

[lemonlimelike]

WStanley, спасибо

Answer 2

[Sandy_Kingdom]

SELECT e.*
FROM employee e
INNER JOIN (SELECT*FROM appointment WHERE start_time BETWEEN '09:00:00' AND'09:01:00') a ON a.service_id = e.service_id AND a.employee_id = e.employee_id
LEFT JOIN (SELECT employee_id, service_id, COUNT (id) AS count_empl FROM appointment GROUP BY employee_id, service_id) a2 ON a2.employee_id = e.employee_id AND a2.service_id = e.service_id
WHERE a2.count_empl <= 3