Group by查询结果因服务器而异

Question

我有两个表要在一个属性(Sensor_id)上联接。然后我想在相同的属性上分组，但我需要的结果是ORDER BY Timestamp DESC属性。因此，我首先使用子查询ORDER BY Timestamp DESC，然后外部查询将按Sensor_id分组

第一个表: 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的时间戳。

EDIT2：

不工作：

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)

Answer 1

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

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

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)上建立索引。