我可以使用子查询代替什么来搜索MAX()? [英] What I can use subquery instead for search MAX()?

问题描述

我有一张表'master_log'，其中存储了所有操作的所有数据.每个记录通过'm_id'连接在一起:

I have table, 'master_log', where I store all data from all action. Each records is connected together by 'm_id':

m_id | value_type_id | value
------------------------------------------
1    | room          | ONE
1    | temperature   | 23
1    | humidity      | 55
1    | timestamp     | 2016-11-01 00:00:01
2    | room          | TWO
2    | timestamp     | 2016-11-01 00:10:51
2    | temperature   | 24
3    | room          | ONE
3    | temperature   | 24
3    | timestamp     | 2016-11-01 00:18:24
------------------------------------------

我需要一号房的最高温度.我现在使用的查询在这里:

I need the maximum temperature from room ONE. The query that I use now is here:

SELECT MAX(value)
FROM master_log
WHERE value_type_id = 'temperature'
    AND m_id IN (SELECT m_id
                    FROM master_log
                    WHERE value = 'ONE')

当我开始测量时，查询是很快的，但是现在有数百万条记录并不太快，我正在寻找最快的方法.有什么想法吗?

When I start measuring, the query is quick, but now with millions of records it is not too quick, and I looking for the quickest method. Any idea please?

推荐答案

例如:

SELECT m_id
     , MAX(CASE WHEN value_type_id = 'room' THEN value END) room
     , MAX(CASE WHEN value_type_id = 'temperature' THEN value END) temperature
     , MAX(CASE WHEN value_type_id = 'humidity' THEN value END) humidity
     , MAX(CASE WHEN value_type_id = 'timestamp' THEN value END) timestamp
  FROM master_log 
 GROUP 
    BY m_id 
HAVING room = 'ONE' 
 ORDER 
    BY temperature DESC 
 LIMIT 1;

...但是，如果正确索引，Martin的解决方案将更快

... but, appropriately indexed, Martin's solution will be faster

这篇关于我可以使用子查询代替什么来搜索MAX()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！