mysql语句嵌套查询语句_MySQL嵌套查询选择语句

I've got a table that helps me keep track of the delay times between my slaves and the master. My question is how can I craft a select statement that:

1. gives me the latest delay values, without repeating (or skipping) ip addresses

2. doesn't need to be updated if I add additional servers, or as servers become unresponsive

The goal of this query is to show me what servers are available to do work, and give me a rough estimate as to how hard they are working. Servers that are not operational, shouldn't appear in the results. I'm running a script to evaluate the delay times as CLI every minute, so if I could limit the possible records returned to the last minute and a half, that should be good enough to tell me which servers were up the last time they were queried.

Table looks like this (columns renamed to protect the innocent):

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

ip VARCHAR( 20 ) NOT NULL ,

sent TIMESTAMP NOT NULL ,

delay DECIMAL ( 7,4 ) NOT NULL ,

status VARCHAR( 100 ) NOT NULL ,

execution_time` DECIMAL ( 7,4 )NOT NULL ,

deleted` TINYINT NOT NULL ,

Any help would be appreciated.

解决方案

After some googling, and some testing, this is the best answer I've found thus so far:

SELECT ip, id, delay, stat_sent

FROM status

WHERE stat_sent > DATE_SUB( NOW( ) , INTERVAL 1 MINUTE )

AND stat_sent

IN (

SELECT max( stat_sent )

FROM status GROUP BY stat_ip

)

Modified my answer now it limits the scope of the results to servers that have been updated in the last minute.