我想返回一个数组的日期和时间范围,当一个网站关闭。
我使用的是MySQL 5.7。
表down_time
created_at user_id down
2017-12-15 14:50:21 1 1
2017-12-21 19:19:19 1 0
2017-12-25 22:41:14 1 1
2017-12-25 22:41:17 1 0
2017-12-25 23:11:22 1 0
2017-12-25 23:11:24 1 1
2017-12-25 23:31:24 1 1这里on down column - 0(false)代表down,1(true)代表up。我需要这样的视图/结果:
down up user_id
2017-12-21 19:19:19 2017-12-25 22:41:14 1
2017-12-25 22:41:17 2017-12-25 23:11:24 1 希望这个例子能完全代表我的查询需求--我只需要停机时间范围。
如果可以使用Laravel(5.5) SQL query helper方法来实现这一点,那就太好了(这样我就可以很容易地添加像->where('user_id', Auth::user()->id)->whereBetween('created_at', [$range['from'], $range['to']])这样的查询选择器),但在这种情况下我并不挑剔--一个原始的MySQL(5.7.19 )查询也会很棒。
发布于 2017-12-26 09:25:01
如果我理解正确的话,您可以通过执行以下操作在MySQL中完成此操作:
select user_id, min(created_at) as ca_0, created_at_1 as ca_1
from (select t.*,
(select min(t2.created_at)
from t t2
where t2.user_id = t.user_id and t2.down = 1 and
t2.created_at > t.created_at
) as created_at_1
from t
where t.down = 0
) tt
group by user_id, created_at_1;我不知道如何用Laravel来表达。
发布于 2017-12-27 09:41:54
为那些希望在他们的mysql查询中实现日期、时间范围和user_id选择器的人提供一个免费的答案。这基本上是我的主要目标:
$range = request('range');
$range_from = $range['from'];
$range_to = $range['to'];
$user_id = Auth::user()->id;
return DB::select("select user_id, min(created_at) as ca_0, created_at_1 as ca_1
from (select down_time.*,
(select min(t2.created_at)
from down_time t2
where t2.user_id = $user_id and t2.down = 1 and
t2.created_at > down_time.created_at and
t2.created_at between '$range_from' and '$range_to'
) as created_at_1
from down_time
where down_time.down = 0 and down_time.user_id = $user_id and
down_time.created_at between '$range_from' and '$range_to'
) tt
group by user_id, created_at_1;");https://stackoverflow.com/questions/47972926
复制相似问题