jtZvgUMa

1CREATE TABLE IF NOT EXISTS `visit` (
2  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
3  `siteId` BIGINT(6) NOT NULL,
4  `clientId` BIGINT(6) NULL,
5  `guest` TINYINT(1) NULL DEFAULT 1,
6  `ip` LONGTEXT  NULL,
7  `os` LONGTEXT NULL,
8  `screen` LONGTEXT  NULL,
9  `page` LONGTEXT NULL,
10  `referer` LONGTEXT NULL,
11  `device` LONGTEXT  NULL,
12  `location` LONGTEXT  NULL,
13  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
14  PRIMARY KEY (`id`, `siteId`),
15  INDEX `fk_visit_site1_idx` (`siteId` ASC),
16  CONSTRAINT `fk_visit_site1`
17    FOREIGN KEY (`siteId`)
18    REFERENCES `site` (`id`)
19    ON DELETE NO ACTION
20    ON UPDATE NO ACTION)
21ENGINE = InnoDB;
22	
23SELECT 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;
24	
25CREATE TABLE IF NOT EXISTS `visit` (
26  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
27  `siteId` BIGINT(6) NOT NULL,
28  `clientId` BIGINT(6) NULL,
29  `guest` TINYINT(1) NULL DEFAULT 1,
30  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
31  PRIMARY KEY (`id`, `siteId`),
32  INDEX `fk_visit_site1_idx` (`siteId` ASC),
33  CONSTRAINT `fk_visit_site1`
34    FOREIGN KEY (`siteId`)
35    REFERENCES `site` (`id`)
36    ON DELETE NO ACTION
37    ON UPDATE NO ACTION)
38ENGINE = InnoDB;
39
40CREATE TABLE IF NOT EXISTS `visitinfo` (
41  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
42  `visitId` BIGINT(6) NOT NULL,
43  `ip` LONGTEXT NULL,
44  `os` LONGTEXT NULL,
45  `screen` LONGTEXT NULL,
46  `page` LONGTEXT NULL,
47  `referer` LONGTEXT NULL,
48  `device` LONGTEXT NULL,
49  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
50  PRIMARY KEY (`id`, `visitId`),
51  INDEX `fk_visitinfo_visit2_idx` (`visitId` ASC),
52  CONSTRAINT `fk_visitinfo_visit2`
53    FOREIGN KEY (`visitId`)
54    REFERENCES `visit` (`id`)
55    ON DELETE NO ACTION
56    ON UPDATE NO ACTION)
57ENGINE = InnoDB;
58	
59SELECT
60  *
61FROM
62  visit
63WHERE
64  ......
65GROUP BY
66  clientId, siteId
67HAVING
68  created = MAX(created);
69	
70SELECT
71  *
72FROM
73  visit
74WHERE
75  clientId = ? AND
76  siteId = ?
77ORDER BY
78  created DESC
79LIMIT 1