Избавится от SELECT в выражении WHERE

23
10 июня 2018, 18:30

Есть такая таблица в БД:

|UserId   |DateTo             |serviceCode|status|lastDate           |
|---------|-------------------|-----------|------|-------------------|
|116750128|2018-03-11 22:00:00|PE0753.24  |1     |2018-02-12 20:31:43|
|116750128|2018-03-29 22:00:00|PE0753.24  |1     |2018-01-29 22:00:00|
|116750128|2038-01-19 06:14:07|PE0753.24  |1     |2018-01-30 22:00:00|

Необходимо выбрать lastDate только у тех записей, у которых DateTo равна максимуму среди DateTo которые относятся к UserId, serviceCode и status.

Т.е. в данном случае должно вернутся 2018-01-30 22:00:00, т.к. DateTo=2038-01-19 06:14:07 максимуму, среди UserId=116750128 и serviceCode=PE0753.24 и status=1.

Смог придумать такой запрос:

SELECT
  p1.lastDate, p1.UserId, p1.serviceCode
FROM Users.UserInfo p1
WHERE
  p1.status= 1
  AND p1.UserId>= 116750128
  AND p1.UserId< 116750129
  AND p1.DateTo= (SELECT max(p2.dateTo) FROM Users.UserInfo p2
                      WHERE p2.UserId= p1.UserId
                            AND p2.status= p1.status
                            AND p2.serviceCode= p1.serviceCode);

Может есть способ получше, не нравится подселект в конструкции WHERE. Данный запрос долго отрабатывает на таблице с порядком 200 млн записей.

Answer 1

Для реализации воспользуемся оператором группировки (GROUP BY), сделаем группировку по userId и serviceCode, по status делать не будем, так как он явно указан в запросе. В SELECT выберем максимальную дату из группы MAX(p1.lastDate).

SELECT
  p1.UserId, p1.serviceCode, MAX(p1.lastDate) maxDate
FROM Users.UserInfo p1
WHERE
  p1.status = 1
  AND p1.UserId >= 116750128
  AND p1.UserId < 116750129
  GROUP BY p1.userId, p1.serviceCode;
Answer 2
SELECT p1.lastDate, p1.UserId, p1.serviceCode
  FROM Users.UserInfo p1
  JOIN (SELECT UserId, serviceCode, max(dateTo) dateTo
          FROM Users.UserInfo
         WHERE UserId >= 116750128 AND UserId < 116750129
           AND status = 1
         GROUP BY UserId, serviceCode
       ) p2
    ON p2.UserId = p1.UserId AND p2.serviceCode = p1.serviceCode
   AND p1.dateTo = p2.dateTo
 WHERE p1.status = 1

Должно работать быстро, при наличии индекса по UserId (В идеале по UserId, serviceCode, dateTo если записей с одним userId очень много, но такой индекс сам по себе очень большой и может тормозить вставки)

READ ALSO
composer install Laravel

composer install Laravel

Клонировал репозиторий с гита, но после ввода в консоль composer install получаю ошибки

52
Открытие статьи из базы данных mysql

Открытие статьи из базы данных mysql

Пытаюсь вывести открытие статьи на страницу, на главную страницу вывести получилось, как превью статьи вывел, не получается сделать чтобы...

50
Почему один код на php и js считает по разному?

Почему один код на php и js считает по разному?

Есть php код для генерации последовательности чисел:

51
Передать значения формы через AJAX в PHP

Передать значения формы через AJAX в PHP

Как передать данные формы в php при помощи AJAX?

29