MySQL嵌套查询选择语句 [英] MySQL Nested Query Select Statement

问题描述

我有一张桌子，可以帮助我跟踪从属设备和主设备之间的延迟时间.我的问题是我该如何编写以下选择语句:
1.给我最新的延迟值，而无需重复(或跳过)ip地址
2.如果我添加了其他服务器，或者服务器不再响应，则不需要更新

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

此查询的目的是向我展示可以使用哪些服务器来工作，并大致估计它们的工作强度.无法运行的服务器不应出现在结果中.我正在运行一个脚本，以每分钟为单位评估CLI的延迟时间，因此，如果我可以将可能返回的记录限制到最后一分钟半，那应该足以告诉我上次服务器已启动了哪些服务器.查询.

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 ,  

任何帮助将不胜感激.

推荐答案

经过一番谷歌搜索和测试之后，这是到目前为止我发现的最佳答案:

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.

这篇关于MySQL嵌套查询选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！