mysql-在时间戳中选择每天的最后一个条目和相应的值
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql-在时间戳中选择每天的最后一个条目和相应的值，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3804字，纯文字阅读大概需要6分钟。
内容图文
我有一个名为“ admin_tmp”的表,结构如下
Field | Type | Null | Key | Default | Extra
----------------------+------------------+------+-----+---------+---------------
id | int(10) unsigned | NO | PRI | NULL | auto_increment
time_stamp | varchar(30) | NO | | NULL |
curr_property | int(5) unsigned | NO | | NULL |
curr_property_cost | int(5) unsigned | NO | | NULL |
day_property | int(S) unsigned | NO | | NULL |
day_property_cost | int(5) unsigned | NO | | NULL |
curr_solar_generating | int(5) unsigned | NO | | NULL |
curr_solar_export | int(5) unsigned | NO | | NULL |
day_solar_generated | int(5) unsigned | NO | | NULL |
day_solar_export | int(5) unsigned | NO | | NULL |
curr_chanl | int(5) unsigned | NO | | NULL |
curr_chan2 | int(5) unsigned | NO | | NULL |
curr_chan3 | int(5) unsigned | NO | | NULL |
day chan1 | int(5) unsigned | NO | | NULL |
day_chan2 | int(5) unsigned | NO | | NULL |
day_chan3 | int(5) unsigned | NO | | NULL |
现在,我想为每天及其对应的值选择两个时间值7：0：0和7:59:59(仅日期而不是时间戳)之间的最后一个条目
我只能使用以下查询获取以下内容,
询问
SELECT id, time_stamp , curr_property, day_property, mytime , mydate
FROM (
SELECT
*
,DATE(time_stamp) AS mydate
,TIME(time_stamp) AS mytime
FROM admin_tmp
) AS Result
WHERE mytime >= '07:00:00' AND mytime <= '07:59:59'
Order By mytime LIMIT 15;
结果
id | time_stamp | curr_property | day_property | mytime | mydate
------+---------------------+---------------+--------------+----------+-----------
1225 | 2014-06-01 07:00:04 | 1641 | 11466 | 07:00:04 | 2014-06-01
13802 | 2014-06-03 07:00:05 | 1850 | 15452 | 07:00:05 | 2014-06-03
7418 | 2014-06-02 07:00:05 | 1577 | 13053 | 07:00:05 | 2014-06-02
1226 | 2014-06-01 07:00:16 | 1593 | 11471 | 07:00:16 | 2014-06-01
13803 | 2014-06-03 07:00:17 | 1577 | 15457 | 07:00:17 | 2014-06-03
7419 | 2014-06-02 07:00:17 | 1528 | 13058 | 07:00:17 | 2014-06-02
1227 | 2014-06-01 07:00:28 | 1577 | 11476 | 07:00:28 | 2014-06-01
7420 | 2014-06-02 07:00:29 | 1545 | 13063 | 07:00:29 | 2014-06-02
13804 | 2014-06-03 07:00:29 | 1850 | 15464 | 07:00:29 | 2014-06-03
1228 | 2014-06-01 07:00:40 | 981 | 11480 | 07:00:40 | 2014-06-01
13805 | 2014-06-03 07:00:41 | 1561 | 15469 | 07:00:41 | 2014-06-03
7421 | 2014-06-02 07:00:41 | 1577 | 13069 | 07:00:41 | 2014-06-02
1229 | 2014-06-01 07:00:52 | 1206 | 11484 | 07:00:52 | 2014-06-01
7422 | 2014-06-02 07:00:53 | 1399 | 13073 | 07:00:53 | 2014-06-02
13806 | 2014-06-03 07:00:53 | 1545 | 15474 | 07:00:53 | 2014-06-02
我知道必须使用join来实现此目的,但是再次我不能没有任何错误地使用join.
解决方法:
您将选择两次相同的数据,一次选择获取记录,一次选择获取每个日期的最大时间.然后将两者都加入,以便进行筛选,例如仅获取每个日期的最大时间的记录.
SELECT
rec.id,
rec.time_stamp,
rec.curr_property,
rec.day_property,
rec.mytime,
rec.mydate
FROM
(
SELECT
admin_tmp.*,
DATE(time_stamp) AS mydate,
TIME(time_stamp) AS mytime
FROM admin_tmp
WHERE TIME(time_stamp) >= '07:00:00' AND TIME(time_stamp) <= '07:59:59'
) as rec
JOIN
(
SELECT
DATE(time_stamp) as mydate,
MAX(TIME(time_stamp)) as mytime
FROM admin_tmp
WHERE TIME(time_stamp) >= '07:00:00' AND TIME(time_stamp) <= '07:59:59'
GROUP BY DATE(time_stamp)
) as max_times ON max_times.mydate = rec.mydate and max_times.mytime = rec.mytime
ORDER BY rec.mytime LIMIT 15;
内容总结
以上是互联网集市为您收集整理的mysql-在时间戳中选择每天的最后一个条目和相应的值全部内容，希望文章能够帮你解决mysql-在时间戳中选择每天的最后一个条目和相应的值所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。