在时间戳和相应的值中选择每天的最后一个条目。
I have a table called 'admin_tmp' with following structure
我有一个名为“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 |
Now I want to select last entry between two time values 7:0:0 and 7:59:59 (only date not the timestamp) for each day and its corresponding values
现在我要选择最后一个条目，在两个时间值7:0:0和7:59:59之间(只有日期不是时间戳)，用于每一天及其相应的值
I am able to fetch only the follow using the below query,
我只能使用下面的查询来获取以下内容，
Query
查询
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;
Result
结果
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
I came to know that I have to use join to achieve this, but again I couldn't use join without any errors.
我开始意识到我必须使用join来实现这个目标，但是同样地，我也不能使用没有任何错误的join。
2 个解决方案
#1
2
You would select the same data twice, one time to get the records, one time to get the max time per date. Then join both, so you filter such as to get the records for the max time per date only.
您将选择相同的数据两次，一次获取记录，一次获取每个日期的最大时间。然后将两者都加入，这样您就可以过滤，以便只获取每个日期的最大时间记录。
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;
更多相关文章
- 为什么这个查询需要很长时间才能执行
- 如何在php中测量mysql时间，sql查询的时间和/或负载？
- MySQL - 更改一行的时间值以匹配同一表的另一行的时间值
- MySQL学习笔记_时间，多表更新，数据库元数据
- 你可以在android webview中自动链接日期吗？
- 函数的作用是:在javascript中将时间戳转换为人工日期
- js和php时间戳的问题
- 从服务器（任何服务器）获取当前日期和时间。仅限javascript
- 输入类型=日期的日期显示为dd-mm-yyyy格式