mysql - 根据服务器的不同，按查询结果分组

Question

我有两个表要加入一个属性（Sensor_id）。然后我想对同一个属性进行 GROUP BY，但我需要结果是 ORDER BY Timestamp DESC 属性。所以我使用子查询首先 ORDER BY Timestamp DESC 然后外部查询将 GROUP BY Sensor_id
First table: Sensors_colocation

=========================================================================================
| Sensor_id | Sensor_longitude | Sensor_latitude | Paese | Pseudonimo | limit1 | limit2 |
=========================================================================================

第二张表：日志

===========================================
| Id | Mac_reali | Mac_random | Timestamp |
===========================================

使用

SELECT * FROM log AS L JOIN Sensors_colocation AS S ON L.Id = S.Sensor_id ORDER BY L.Id ASC, L.Timestamp DESC

我在我拥有的两台服务器上都得到了我想要的东西。问题是当我执行完整查询时

SELECT * FROM (
    SELECT * FROM log AS L JOIN Sensors_colocation AS S ON L.Id = S.Sensor_id 
    ORDER BY L.Id ASC, L.Timestamp DESC) AS temp
GROUP BY temp.Id

在一台服务器上，我得到按时间戳 DESC 排序并按 ID 分组的结果。在另一台服务器（结构相同但数据不同）上，我得到按时间戳 ASC 排序并按 Id 分组的结果。我不明白为什么如果我使用子查询，我在内部查询中的 ORDER BY 不会被考虑。你能帮助我吗？

编辑：我的目标是拥有连接表的所有属性，但只有最后一个条目谈到每个 ID 的时间戳。

编辑2：

不工作：

10.1.41-MariaDB-0+deb9u1

CREATE TABLE `log` (
  `Id` int(11) NOT NULL,
  `Mac_reali` int(11) NOT NULL,
  `Mac_random` int(11) NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `log`
  ADD PRIMARY KEY (`Id`,`Timestamp`);

CREATE TABLE `Sensors_colocation` (
  `Sensor_id` int(11) NOT NULL,
  `Sensor_longitude` decimal(7,6) NOT NULL,
  `Sensor_latitude` decimal(8,6) NOT NULL,
  `Paese` varchar(32) NOT NULL,
  `Pseudonimo` varchar(32) NOT NULL,
  `limit1` int(11) NOT NULL,
  `limit2` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `Sensors_colocation`
  ADD PRIMARY KEY (`Sensor_id`);

在职的：

5.6.33-log


CREATE TABLE IF NOT EXISTS `log` (
  `Id` int(11) NOT NULL,
  `Mac_reali` int(11) NOT NULL,
  `Mac_random` int(11) NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`,`Timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Sensors_colocation` (
  `Sensor_id` int(11) NOT NULL,
  `Sensor_longitude` decimal(7,6) NOT NULL,
  `Sensor_latitude` decimal(8,6) NOT NULL,
  `Paese` varchar(32) NOT NULL,
  `Pseudonimo` varchar(32) NOT NULL,
  `limit1` int(11) NOT NULL,
  `limit2` int(11) NOT NULL,
  PRIMARY KEY (`Sensor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

EDIT3：考虑内部查询的输出（我不写一些我们不需要的属性）

Id | Mac_reali | Timestamp | Sensor_id | Pseudonimo

1 | 30 | "2019-09-29 17:27:33" | 1 | Manarola(Stazione)
1 | 23 | "2019-09-29 17:25:33" | 1 | Manarola(Stazione)
1 | 57 | "2019-09-29 17:23:33" | 1 | Manarola(Stazione)
2 | 12 | "2019-09-29 17:28:42" | 2 | Vernazza(Stazione)
2 | 33 | "2019-09-29 17:26:42" | 2 | Vernazza(Stazione)
2 | 12 | "2019-09-29 17:24:42" | 2 | Vernazza(Stazione)
3 | 23 | "2019-09-29 17:33:42" | 3 | Monterosso(Stazione)
3 | 17 | "2019-09-29 17:31:42" | 3 | Monterosso(Stazione)
3 | 16 | "2019-09-29 17:29:42" | 3 | Monterosso(Stazione)

从“工作”服务器，从我得到的外部查询

Id | Mac_reali | Timestamp | Sensor_id | Pseudonimo

1 | 30 | "2019-09-29 17:27:33" | 1 | Manarola(Stazione)
2 | 12 | "2019-09-29 17:28:42" | 2 | Vernazza(Stazione)
3 | 23 | "2019-09-29 17:33:42" | 3 | Monterosso(Stazione)

从“不工作”的服务器我得到相反的时间戳（好像 ORDER BY 被忽略了）

Id | Mac_reali | Timestamp | Sensor_id | Pseudonimo

1 | 57 | "2019-09-29 17:23:33" | 1 | Manarola(Stazione)
2 | 12 | "2019-09-29 17:24:42" | 2 | Vernazza(Stazione)
3 | 16 | "2019-09-29 17:29:42" | 3 | Monterosso(Stazione)

标签： mysqlsqlphpmyadminsql-order-by

Answer 1

我的目标是拥有连接表的所有属性，但只有最后一个条目谈到每个 Id 的时间戳。

考虑这种使用相关子查询来确保没有其他log记录id与更大的相同的方法timestamp：

SELECT * 
FROM log l
INNER JOIN sensors_colocation s ON l.id = s.sensor_id 
WHERE NOT EXISTS (
    SELECT 1
    FROM log l1
    WHERE l1.id = l.id AND l1.timestamp > l.timestamp
)
ORDER BY l.id ASC, l.timestamp DESC

如果您正在运行 MySQL 8.0，则可以通过使用窗口函数ROW_NUMBER()对具有相同 的记录组中的时间戳降序排列记录来获得相同的结果id，然后过滤每个组的顶部记录：

SELECT *
FROM (
    SELECT 
        l.*, 
        s.*,
        ROW_NUMBER() OVER(PARTITION BY l.id ORDER BY l.timestamp DESC) rn
    FROM log l
    INNER JOIN sensors_colocation s ON l.id = s.sensor_id 
) x
WHERE rn = 1

注意：为了提高性能，您需要在log(id, timestamp).