MySQL - 选择检索上次日期时间 [英] MySQL - Select to retrieve last datetime

问题描述

我在 MySQL 中有一个表:

I've got a table in MySQL:

| period_duration    | duration | sample | corner | country | partner       | ok_rate
+---------------------+----------+--------+----------+---------+-----------------------+-------------------------
| 2014-12-15 17:00:00 | 3600     | 1      | GRPS_INB | ARG     | Charlie   |     98   | 
| 2014-12-15 17:00:00 | 3600     | 1      | GRPS_INB | DEU     | Jack      |    90    |
| 2014-12-15 17:00:00 | 3600     | 1      | GRPS_INB | NLD     | Will      |    100   |
| 2014-12-15 20:00:00 | 3600     | 1      | GRPS_INB | ARG     | Charlie   |     98   |
| 2014-12-15 20:00:00 | 3600     | 1      | GRPS_INB | DEU     | Jack      |     90   |
| 2014-12-15 20:00:00 | 3600     | 1      | GRPS_INB | NLD     | Will      |    100   |

+-----------------+-------------+------+-----+---------------------+-------+
| Field           | Type        | Null | Key | Default             | Extra |
+-----------------+-------------+------+-----+---------------------+-------+
| period_duration | datetime    | NO   | PRI | 0000-00-00 00:00:00 |       |
| duration        | varchar(6)  | YES  |     | NULL                |       |
| sample          | varchar(2)  | YES  |     | NULL                |       |
| corner          | varchar(10) | YES  |     | NULL                |       |
| country         | varchar(60) | NO   | PRI |                     |       |
| partner         | varchar(60) | NO   | PRI |                     |       |
| ok_rate         | int(8)      | YES  |     | NULL                |       |
+-----------------+-------------+------+-----+---------------------+-------+

此表逐小时增加.我需要一个 shellscript 来检查上一小时的每一行，并且ok_rate"低于给定值，然后在选择时将它们返回给我.选择应包含国家/地区、合作伙伴和 ok_rate.

This table increases from hour to hour. I need a shellscript that will check each line from the last hour and that has an "ok_rate" lower than a given value, and return them to me on a select. The select should bring country, the partner, and the ok_rate.

示例:

假设我希望它在ok_rate"上用 <=98%¨on 完成.我返回的行是:

Let's say I want it to be done with <=98%¨on "ok_rate". My returning rows would be:

| 2014-12-15 20:00:00 | 3600     | 1      | GRPS_INB | ARG     | Charlie   |     98   |
| 2014-12-15 20:00:00 | 3600     | 1      | GRPS_INB | DEU     | Jack      |     90   |

到目前为止，我只能让它给我带来低于 98% 的值，但不能让那些也是存在的最后日期时间的值:

So far, I could only make it bring me the values below 98%, but not those that are also the last datetime that exists:

select country, partner, ok_rate from TABLE where ok_rate <= '98'

但是我怎样才能添加一个 WHERE 子句，让它明白我想要最后一个存在于 period_duration 上的 datetime 呢?

But how can I add a WHERE clause the makes it understand I want the last existing datetime that exists on period_duration too?

我的意思是:

SELECT country, partner, ok_rate FROM TABLE WHERE pdp_in_ok_rate <=98 AND (period_duration is the last one)?

推荐答案

这个查询应该返回你想要的:

This query should return what you want:

SELECT country, partner, ok_rate
FROM TABLE WHERE ok_rate <= 98
AND period_duration = (SELECT MAX(period_duration)
                       FROM TABLE)

这篇关于MySQL - 选择检索上次日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！