+-----------+-----------+----------+
| TIMESTAMP | MACHNE_NO | ERR_CODE |
+-----------+-----------+----------+
| 9:00:00.0 | 17 | 100 |
| 9:00:00.1 | 15 | 58 |
| 9:00:00.1 | 17 | 68 |
| 9:00:01.5 | 9 | 18 |
| 9:00:01.6 | 9 | 100 |
| 9:00:01.9 | 9 | 95 |
| 9:00:02.7 | 15 | 18 |
| 9:00:02.8 | 17 | 18 |
| 9:00:03.2 | 17 | 22 |
+-----------+-----------+----------+
我有上面这样的数据,我想在机器出现ERR_CODE 100后三秒钟选择包含错误的行。因此,在我所示的表格中,第3行和第8行应选择用于机器17,第5行应选择机器9。有人能给我看一个SQL查询吗?
反转问题并在每次错误前的3秒内查找错误代码100。为此,请使用窗口函数和筛选。对于带有错误的行:
select t.*
from (select t.*,
countif(err_code = 100) over (
partition by machine_no
order by unix_seconds(timestamp)
range between 3 preceding and current row
) as cnt_100
from t
) t
where cnt_100 > 0 and err_code <> 100;
请注意,任何具有err_code = 100
的行都将满足countif()
条件。但是,它们会被外部where
子句过滤掉。
$machine no
是您想要获得机器id 的变量
"SELECT * FROM `table` WHERE `MACHNE_NO`='$machine_no' AND `ERR_CODE`='100' AND `TIMESTAMP`>=DATEADD(ss,-3,NOW()) AND `TIMESTAMP`<=NOW()"