Pull to refresh

Эмуляций ROW_NUMBER в MySQL

Статья написана по мотивам этого замечательного топика. Нашел я его гуглем, прочитал, и решения, которое меня бы устраивало, к сожалению, не нашел.

Пришлось думать самому. Обидно было то, что основная моя работа связана Microsoft SQL Server, в котором данная задача решается элементарно c помощью функции ROW_NUMBER.

Задача, которую ставит автор в конце на 99% совпала с моей.

Сформулирую её, в несколько ином виде. Допустим, есть две таблицы: новости и комментарии к ним. Нужно выбрать одним запросом последние двадцать новостей (отсортированые по убыванию) и для каждой новости 10 последних комментариев, отсортированных по возрастанию.

Конечно, до элегантности синтаксиса T-SQL'a данному решению как до луны, но что делать. Буду очень признателен, если кто-то предложит более простое, но не менее быстрое решение.

Итак, код.



Для начала DDL:

CREATE TABLE `post` (
`ID_Post` int(11) NOT NULL AUTO_INCREMENT,
`Post` mediumtext,
`Date_Creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID_Post`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_comment` (
`ID_Comment` bigint(20) NOT NULL AUTO_INCREMENT,
`ID_Post` int(11) NOT NULL,
`Comment` mediumtext,
`Date_Creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID_Comment`),
KEY `ID_Post` (`ID_Post`),
CONSTRAINT `post_comment_fk` FOREIGN KEY (`ID_Post`) REFERENCES `post` (`ID_Post`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



И, собственно, всего один запрос:

SELECT NULL INTO @Last_ID; -- На всякий случай очищаем переменную
SELECT 
  ID_Post, Post, Date_Creation, ID_Comment, Comment, Comment_Date, rn
FROM (
  SELECT 
    *,
    @Reset_Num := CASE -- Признак смены поста
      WHEN @Last_ID = ID_Post 
      THEN 1 
      ELSE 0 
    END AS Reset_Num,
    -- Идентификатор последнего выбранного поста, ценен тем, 
     -- что выбирается после расчёта @Reset_Num
    @Last_ID := ID_Post, 
    -- ROW_NUMBER() OVER 
    -- (PARTITION BY ID_Post ORDER BY c.Date_Creation DESC)
    @rn := CASE 
      WHEN @Reset_Num = 0
      THEN 0
      ELSE @rn + 1
    END AS rn
  FROM (
    SELECT 
      pp.ID_Post, 
      pp.Date_Creation,
      pp.Post,
      c.ID_Comment,
      c.`Comment`,
      c.Date_Creation AS Comment_Date
      FROM 
      (
        SELECT * FROM post
        /* WHERE ID_User = 441 -- например */
        ORDER BY Date_Creation DESC
        LIMIT 0, 20
      ) AS pp
      LEFT JOIN post_comment AS c
        ON c.ID_Post = pp.ID_Post
      ORDER BY pp.ID_Post, c.ID_Comment DESC
  ) AS t 
) AS tt
WHERE rn < 10
ORDER BY ID_Post, rn DESC

Tags:
Hubs:
You can’t comment this publication because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author’s username will be hidden by an alias.