主页 / 问题 / 1571001
Accepted
Alexey
Asked:2024-03-11 04:39:13 +0000 UTC2024-03-11 04:39:13 +0000 UTC

SQL。请求显示最新值

  • 772

有两个表（服务器版本：10.6.16-MariaDB-0ubuntu0.22.04.1 - Ubuntu 22.04），内容如下：

传感器表

传感器 ID 传感器地址
1 03173311a0ff
2 xxxxxxxxxxxx

...以及测量表

测量ID 传感器 ID 温度值 测量时间
1 1 18.567 2024-03-10 19:00:30
2 2 19.123 2024-03-10 19:00:40
3 1 20.0 2024-03-10 20:00:30
4 2 22.5 2024-03-10 20:00:40

任务： 显示所有传感器的最新温度（Temperature_value）和传感器地址（sensor_adress），即 像这样的东西：

传感器地址 温度值
03173311a0ff 20.0
xxxxxxxxxxxx 22.5

我尝试创建一个 SQL 查询，这就是我得到的结果：

SELECT `sensors`.`sensor_adress`, `measurements`.`temperature_value`
FROM `measurements` INNER JOIN `sensors`
ON `sensors`.`sensor_id` = `measurements`.`sensor_id`
WHERE `measurements`.`measurement_time` = (SELECT MAX(`measurement_time`) FROM `measurements`)
ORDER BY `sensors`.`sensor_id` ASC;

如何改进子查询？

SELECT MAX(`measurement_time`) FROM `measurements`

这样就不是根据最大时间戳进行选择，而是根据每个传感器最相关的时间戳进行选择？

sql

1 个回答

  1. Best Answer

    为了解决此类问题，通常可以采用以下方案：

    1. 使用派生表/通用表表达式进行分组来查找传感器的最大值，并将这些内容连接到数据表本身。像那样：

      SELECT s.address, d.temperature_value
FROM sensors AS s 
INNER JOIN (
   SELECT sensor_id, max(id) as id
   FROM measurements
   GROUP BY sensor_id
) AS m ON (m.sensor_id = s.id)
INNER JOIN measurements AS d ON (d.id = m.id)

# ---- либо аналогичный CTE
WITH m AS (
    SELECT sensor_id, MAX(id) AS id
    ....
)
SELECT 
FROM sensors AS s 
INNER JOIN m ....

      这里的连接类型将决定首先出现的内容，即可用数据或传感器的列表。

    2. 窗口函数和行编号

      WITH data AS (
   SELECT sensor_id
       , temperature_value
       , ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY id DESC) AS rn
   FROM measurements AS m
) 
SELECT s.address, data.temperature_value
FROM data 
INNER JOIN sensors AS s ON (s.id = data.sensor_id)
WHERE rn = 1

    3. 返回传感器单个温度值的关联子查询：

      SELECT s.address
    , (SELECT temperature_value
       FROM measurements AS m
       WHERE m.sensor_id = s.id
       ORDER BY id DESC 
       LIMIT 1) AS t
FROM sensors AS s

    4. 如果除了一个值之外，您还需要从行中提取其他数据，则子查询将进入联接

       SELECT s.address, m.temperature_value
 FROM sensors AS s 
 LEFT JOIN measurements AS m ON (
       m.id = (SELECT MAX(id) 
               FROM measurements AS m 
               WHERE m.sensor_id = s.id)
 )

    对于您的情况和所需的结果，显然最简单的选项是选项 3。

    • 4

相关问题