多个MYSQL查询到多列表 [英] Multiple MYSQL queries into multiple columns table

问题描述

我有三个表，设置如下:

I have three tables, with the following setup:

Ph
  ID
  time (in the format: datetime)
  Ph

Total suspended solids
  ID
  time (in the format: datetime)
  solids

TEMPERATURE
  ID
  time (in the format: datetime)
  temp

温度、ph 值和固体这三个值按照下面的示例彼此相隔几秒进行更新，

the three values being, temperature, ph and solids are updated seconds apart from each other as per sample below,

ID          time                    temp
123335  2013-03-29 21:34:05         26.56
123336  2013-03-29 21:35:05         26.56
123337  2013-03-29 21:36:05         26.56

ID          time                    solids
113886  2013-03-29 21:34:19         146
113887  2013-03-29 21:35:19         146
113888  2013-03-29 21:36:19         146

ID          time                    Ph
62147   2013-03-29 21:34:04         7.39
62148   2013-03-29 21:35:04         7.43
62149   2013-03-29 21:36:04         7.43

我想查询mysql得到下面的结果(注意我省略了表中的秒数)

I would like to query mysql to obtain the result below (note I have omitted the seconds from the table)

time                temp    solids  Ph
2013-03-29 21:34    26.56   146     7.39
2013-03-29 21:35    26.56   146     7.43
2013-03-29 21:36    26.56   146     7.43

请问你能帮我查询吗?

推荐答案

好的，查询优化器似乎对我之前对大表的回答有一些问题.试试这个解决方案，它适用于依赖子查询:

Ok, the query optimizer seems to have some problems with my previous answer with large tables. Try this solution instead, it works with dependent subqueries:

SELECT DATE_FORMAT(timeTable.minuteTime, '%Y-%m-%d %k:%i') time,
T2.temp temp,
S2.solids solids,
P2.Ph Ph
FROM
(
    SELECT minuteTime.minuteTime minuteTime,
    ( SELECT MAX(time) FROM temperature WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 HOUR) tempTime, 
    ( SELECT MAX(time) FROM ph WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 HOUR) phTime,  
    ( SELECT MAX(time) FROM solids WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 HOUR) solidsTime
    FROM  
    (
        SELECT time + INTERVAL 59 - SECOND( time ) SECOND minuteTime
        FROM Ph
        WHERE time >= NOW() - INTERVAL 1 HOUR AND time <= NOW()
        UNION SELECT time + INTERVAL 59 - SECOND( time ) SECOND
        FROM solids
        WHERE time >= NOW() - INTERVAL 1 HOUR AND time <= NOW()
        UNION SELECT time + INTERVAL 59 - SECOND( time ) SECOND
        FROM temperature
        WHERE time >= NOW() - INTERVAL 1 HOUR AND time <= NOW()
        GROUP BY 1
    ) minuteTime
) timeTable
LEFT JOIN temperature T2 ON T2.time = timeTable.tempTime
LEFT JOIN solids S2 ON S2.time = timeTable.solidsTime
LEFT JOIN ph P2 ON P2.time = timeTable.phTime
ORDER BY minuteTime ASC

我已经设置了三个表，每个表包含大约 800'000 行测试数据.在 MySQL 5.5.30 上，上面的查询在大约 3.5 秒内运行并返回 61 个结果行.但是你绝对必须在每个时间列上都有索引:

I've setup the three tables with about 800'000 rows of test data, each. On MySQL 5.5.30, the query above runs in about 3.5 seconds and returns 61 result rows. But you absolutely have to have indexes on each of the time columns:

ALTER TABLE `ph` ADD INDEX ( `time` ) ;
ALTER TABLE `solids` ADD INDEX ( `time` ) ;
ALTER TABLE `temperature` ADD INDEX ( `time` ) ;

否则，查询不会终止.我相信没有查询会处理如此大量的数据.

Otherwise, the query won't terminate. I believe no query handling this amount of data will.

这篇关于多个MYSQL查询到多列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！