Выбрать последнюю запись по одному полю MySql

339

26 августа 2018, 12:40

Имеется таблица:

CREATE TABLE IF NOT EXISTS `visit` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `siteId` BIGINT(6) NOT NULL,
  `clientId` BIGINT(6) NULL,
  `guest` TINYINT(1) NULL DEFAULT 1,
  `ip` LONGTEXT  NULL,
  `os` LONGTEXT NULL,
  `screen` LONGTEXT  NULL,
  `page` LONGTEXT NULL,
  `referer` LONGTEXT NULL,
  `device` LONGTEXT  NULL,
  `location` LONGTEXT  NULL,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `siteId`),
  INDEX `fk_visit_site1_idx` (`siteId` ASC),
  CONSTRAINT `fk_visit_site1`
    FOREIGN KEY (`siteId`)
    REFERENCES `site` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

в неё докидываются записи но эти записи могут иметь одинаковые поля siteId и clientId.

Мне нужно выбрать самые последние записи по каждому clientId. siteId мы задаём сами.

Я сделал такое:

SELECT v.id AS id, v.siteId, v.clientId, v.guest, MAX(v.ip) AS ip, MAX(v.os) AS os, MAX(v.screen) AS screen, MAX(v.device) AS device, MAX(v.location) AS location, MAX(v.date) AS date, MAX(v.time) AS time, MAX(v.created) AS created FROM visit AS v WHERE siteId = ? GROUP BY v.id, v.siteId, v.clientId ORDER BY MAX(v.created) DESC;

Но происходят дублирования, да и MAX функция как-то не так выбирает.

Подскажите пожалуйста поправленный SQL запрос.

P.S может есть резон вообще сделать две таблицы?

CREATE TABLE IF NOT EXISTS `visit` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `siteId` BIGINT(6) NOT NULL,
  `clientId` BIGINT(6) NULL,
  `guest` TINYINT(1) NULL DEFAULT 1,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `siteId`),
  INDEX `fk_visit_site1_idx` (`siteId` ASC),
  CONSTRAINT `fk_visit_site1`
    FOREIGN KEY (`siteId`)
    REFERENCES `site` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `visitinfo` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `visitId` BIGINT(6) NOT NULL,
  `ip` LONGTEXT NULL,
  `os` LONGTEXT NULL,
  `screen` LONGTEXT NULL,
  `page` LONGTEXT NULL,
  `referer` LONGTEXT NULL,
  `device` LONGTEXT NULL,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `visitId`),
  INDEX `fk_visitinfo_visit2_idx` (`visitId` ASC),
  CONSTRAINT `fk_visitinfo_visit2`
    FOREIGN KEY (`visitId`)
    REFERENCES `visit` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

SELECT
  *
FROM
  visit
WHERE
  ......
GROUP BY
  clientId, siteId
HAVING
  created = MAX(created);

Это если интересует выборка по всем группам. Если же нужна выборка последней записи в конкретной группе, то так

SELECT
  *
FROM
  visit
WHERE
  clientId = ? AND
  siteId = ?
ORDER BY
  created DESC
LIMIT 1

И не плохо было бы добавить индекс по полю created

Answer 2

CREATE TABLE IF NOT EXISTS `visit` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `siteId` BIGINT(6) NOT NULL,
  `clientId` BIGINT(6) NULL,
  `guest` TINYINT(1) NULL DEFAULT 1,
  `ip` LONGTEXT  NULL,
  `os` LONGTEXT NULL,
  `screen` LONGTEXT  NULL,
  `page` LONGTEXT NULL,
  `referer` LONGTEXT NULL,
  `device` LONGTEXT  NULL,
  `location` LONGTEXT  NULL,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `siteId`));

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 1);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 2);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 1);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (1, 2);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 1);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 1);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 2);

INSERT INTO `visit` (`siteId`, `clientId`) VALUES (3, 2);

SELECT 
  `id`,
  `siteId`,
  `clientId`,
  `guest`,
  `created`
FROM `visit`

id | siteId | clientId | guest | created                   
-: | -----: | -------: | ----: | :-------------------------
 1 |      1 |        1 |     1 | 2018-07-06 20:08:46.040493
 2 |      1 |        2 |     1 | 2018-07-06 20:08:46.088735
 3 |      1 |        1 |     1 | 2018-07-06 20:08:46.117578
 4 |      1 |        2 |     1 | 2018-07-06 20:08:46.136165
 5 |      3 |        1 |     1 | 2018-07-06 20:08:46.149508
 6 |      3 |        1 |     1 | 2018-07-06 20:08:46.168141
 7 |      3 |        2 |     1 | 2018-07-06 20:08:46.219538
 8 |      3 |        2 |     1 | 2018-07-06 20:08:46.235341

Для всех siteId

SELECT 
  V.`id`,
  V.`siteId`,
  V.`clientId`,   
  V.`created`
FROM `visit` AS V
  JOIN 
  (
  SELECT 
    `siteId`,
    `clientId`,    
    MAX(`created`) AS `created`
  FROM `visit`
  GROUP BY
    `siteId`,
    `clientId`    
  ) AS VMAX 
  ON 
    V.`siteId` = VMAX.`siteId` AND 
    V.`clientId` = VMAX.`clientId` AND 
    V.`created` = VMAX.`created`

id | siteId | clientId | created                   
-: | -----: | -------: | :-------------------------
 3 |      1 |        1 | 2018-07-06 20:08:46.117578
 4 |      1 |        2 | 2018-07-06 20:08:46.136165
 6 |      3 |        1 | 2018-07-06 20:08:46.168141
 8 |      3 |        2 | 2018-07-06 20:08:46.235341

Для siteId = 1

SELECT 
  V.`id`,
  V.`clientId`,   
  V.`created`
FROM `visit` AS V
  JOIN 
  (
  SELECT 
    `clientId`,
    MAX(`created`) AS `created`
  FROM `visit`
  WHERE
    `siteId` = 1   -- ВЫБОР siteId
  GROUP BY
    `clientId`
  ) AS VMAX 
  ON 
    V.`clientId` = VMAX.`clientId` AND 
    V.`created` = VMAX.`created`

id | clientId | created                   
-: | -------: | :-------------------------
 3 |        1 | 2018-07-06 20:08:46.117578
 4 |        2 | 2018-07-06 20:08:46.136165

db<>fiddle here