时间戳

首页 > 解决方案 > MySQL 查询优化 LEFT JOIN 和 DISTINCT

mysql - MySQL 查询优化 LEFT JOIN 和 DISTINCT

问题描述

我在 MySQL 中有两个表：

system

Id_system   Name        Type
------------------------------
1           'System1'   'Type1'
2           'System2'   'Type1'
3           'System3'   'Type1'
4           'System4'   'Type2'

measures

Id_system   Sensor  Value   Insert_date
--------------------------------------------------
1           'S1'    12      '2019-23-04 15:16:05'
1           'S2'    2       '2019-23-04 15:16:05'
1           'S3'    42      '2019-23-04 15:16:05'
2           'S1'    11      '2019-23-04 15:11:05'
2           'S2'    3       '2019-23-04 15:11:05'
2           'S3'    43      '2019-23-04 15:11:05'
4           'S1'    2       '2019-23-04 15:10:05'
4           'S2'    32      '2019-23-04 15:10:05'
4           'S3'    2       '2019-23-04 15:10:05'
3           'S1'    32      '2019-23-04 15:06:05'
3           'S2'    54      '2019-23-04 15:06:05'
3           'S3'    15      '2019-23-04 15:06:05'
1           'S1'    10      '2019-23-04 15:00:05'
1           'S2'    1       '2019-23-04 15:00:05'
1           'S3'    35      '2019-23-04 15:00:05'

作为结果，我希望每个系统的最后措施，如下所示：

Id_system   Sensor  Value   Insert_date             Name        
---------------------------------------------------------------
1           'S1'    12      '2019-23-04 15:16:05'   'System1'
1           'S2'    2       '2019-23-04 15:16:05'   'System1'
1           'S3'    42      '2019-23-04 15:16:05'   'System1'
2           'S1'    11      '2019-23-04 15:11:05'   'System2'
2           'S2'    3       '2019-23-04 15:11:05'   'System2'
2           'S3'    43      '2019-23-04 15:11:05'   'System2'
3           'S1'    32      '2019-23-04 15:06:05'   'System3'
3           'S2'    54      '2019-23-04 15:06:05'   'System3'
3           'S3'    15      '2019-23-04 15:06:05'   'System3'

我在 sql 中尝试了这个请求：

SELECT DISTINCT m.sensor, s.name, m.value, m.date_insert 
FROM system s 
LEFT JOIN measure m ON s.id_system = m.id_system 
WHERE s.type = 'Type1'

但她返回，每个系统的每个措施，而我只想要每个系统的每种类型的最后一个措施：

Id_system   Sensor  Value   Insert_date             Name        
---------------------------------------------------------------
1           'S1'    12      '2019-23-04 15:16:05'   'System1'
1           'S2'    2       '2019-23-04 15:16:05'   'System1'
1           'S3'    42      '2019-23-04 15:16:05'   'System1'
2           'S1'    11      '2019-23-04 15:11:05'   'System2'
2           'S2'    3       '2019-23-04 15:11:05'   'System2'
2           'S3'    43      '2019-23-04 15:11:05'   'System2'
3           'S1'    32      '2019-23-04 15:06:05'   'System3'
3           'S2'    54      '2019-23-04 15:06:05'   'System3'
3           'S3'    15      '2019-23-04 15:06:05'   'System3'
1           'S1'    10      '2019-23-04 15:00:05'   'System1'
1           'S2'    1       '2019-23-04 15:00:05'   'System1'
1           'S3'    35      '2019-23-04 15:00:05'   'System1'

你知道怎么做吗？

标签： mysqlsqlleft-joindistinct

解决方案

这是一个典型的最大每组问题

select m.id_system, m.sensor, m.value, m.insert_date, s.name, s.type
join measures m
join system s on m.id_system = s.id_system
from (
    select m.id_system, m.sensor, max(m.insert_date) max_insert_date
    from measures m
    join system s on m.id_system = s.id_system and s.type = 'Type1'
    group by m.id_system, m.sensor
) t on t.id_system = m.id_system and 
       t.sensor = m.sensor and
       t.max_insert_date = m.insert_date
where s.type = 'Type1'

推荐阅读