文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >按步骤和范围选择数据

问按步骤和范围选择数据
EN

Database Administration用户
提问于 2016-10-12 02:22:25
回答 1查看 26关注 0票数 0

我有下面的GPS数据。我试图根据引擎状态获取用户的行程，并按日期排序，其中engine=1是引擎启动，而engine=2是引擎关闭事件，所以每次我发现一个engine=2时，我都知道这是一次旅行的结束。我需要以JSON格式显示这些旅行(我正在使用php进行此操作)，但我不得不单独获得实际的旅行。

任何帮助都将不胜感激。

代码语言：javascript
复制
mysql> select imei, lat, lon, date, engine from dataGps where imei = '864251020174383';

+-----------------+-----------+------------+---------------------+--------+
| imei            | lat       | lon        | date                | engine |
+-----------------+-----------+------------+---------------------+--------+
| 864251020174383 | 12.137000 | -86.254501 | 2016-10-04 14:15:21 |      1 |
| 864251020174383 | 12.135400 | -86.253342 | 2016-10-04 14:16:24 |      1 |
| 864251020174383 | 12.134140 | -86.251671 | 2016-10-04 14:19:12 |      1 |
| 864251020174383 | 12.134770 | -86.250549 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.135820 | -86.249687 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.136580 | -86.248581 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137000 | -86.247551 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137160 | -86.246262 | 2016-10-04 14:20:03 |      1 |
| 864251020174383 | 12.137080 | -86.245621 | 2016-10-04 14:22:33 |      1 |
| 864251020174383 | 12.136490 | -86.243942 | 2016-10-04 14:23:28 |      1 |
| 864251020174383 | 12.135990 | -86.243080 | 2016-10-04 14:43:05 |      1 |
| 864251020174383 | 12.135820 | -86.241798 | 2016-10-04 14:43:57 |      1 |
| 864251020174383 | 12.135820 | -86.240211 | 2016-10-04 14:47:04 |      1 |
| 864251020174383 | 12.132720 | -86.237892 | 2016-10-04 14:49:02 |      1 |
| 864251020174383 | 12.133971 | -86.238281 | 2016-10-04 15:11:27 |      2 |
| 864251020174383 | 12.104250 | -86.253792 | 2016-10-11 20:01:36 |      1 |
| 864251020174383 | 12.105340 | -86.251129 | 2016-10-11 20:01:45 |      1 |
| 864251020174383 | 12.106010 | -86.249069 | 2016-10-11 20:02:02 |      1 |
| 864251020174383 | 12.102820 | -86.245644 | 2016-10-11 20:02:20 |      1 |
| 864251020174383 | 12.087050 | -86.231468 | 2016-10-11 20:02:32 |      1 |
| 864251020174383 | 12.065980 | -86.212334 | 2016-10-11 20:02:48 |      1 |
| 864251020174383 | 12.065560 | -86.208298 | 2016-10-11 20:02:56 |      1 |
| 864251020174383 | 12.064720 | -86.205040 | 2016-10-11 20:03:12 |      1 |
| 864251020174383 | 12.064050 | -86.202888 | 2016-10-11 20:03:20 |      2 |
+-----------------+-----------+------------+---------------------+--------+

旅行1：

代码语言：javascript
复制
| 864251020174383 | 12.137000 | -86.254501 | 2016-10-04 14:15:21 |      1 |
| 864251020174383 | 12.135400 | -86.253342 | 2016-10-04 14:16:24 |      1 |
| 864251020174383 | 12.134140 | -86.251671 | 2016-10-04 14:19:12 |      1 |
| 864251020174383 | 12.134770 | -86.250549 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.135820 | -86.249687 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.136580 | -86.248581 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137000 | -86.247551 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137160 | -86.246262 | 2016-10-04 14:20:03 |      1 |
| 864251020174383 | 12.137080 | -86.245621 | 2016-10-04 14:22:33 |      1 |
| 864251020174383 | 12.136490 | -86.243942 | 2016-10-04 14:23:28 |      1 |
| 864251020174383 | 12.135990 | -86.243080 | 2016-10-04 14:43:05 |      1 |
| 864251020174383 | 12.135820 | -86.241798 | 2016-10-04 14:43:57 |      1 |
| 864251020174383 | 12.135820 | -86.240211 | 2016-10-04 14:47:04 |      1 |
| 864251020174383 | 12.132720 | -86.237892 | 2016-10-04 14:49:02 |      1 |
| 864251020174383 | 12.133971 | -86.238281 | 2016-10-04 15:11:27 |      2 |

旅行2：

代码语言：javascript
复制
| 864251020174383 | 12.104250 | -86.253792 | 2016-10-11 20:01:36 |      1 |
| 864251020174383 | 12.105340 | -86.251129 | 2016-10-11 20:01:45 |      1 |
| 864251020174383 | 12.106010 | -86.249069 | 2016-10-11 20:02:02 |      1 |
| 864251020174383 | 12.102820 | -86.245644 | 2016-10-11 20:02:20 |      1 |
| 864251020174383 | 12.087050 | -86.231468 | 2016-10-11 20:02:32 |      1 |
| 864251020174383 | 12.065980 | -86.212334 | 2016-10-11 20:02:48 |      1 |
| 864251020174383 | 12.065560 | -86.208298 | 2016-10-11 20:02:56 |      1 |
| 864251020174383 | 12.064720 | -86.205040 | 2016-10-11 20:03:12 |      1 |
| 864251020174383 | 12.064050 | -86.202888 | 2016-10-11 20:03:20 |      2 |
range-types
order-by
EN

回答 1

Database Administration用户

发布于 2017-03-13 23:50:13

使用一个变量来设置组(trip)，并在每次引擎=2时增加它的值

代码语言：javascript
复制
select imei, lat, lon, date, engine, 
       if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as trip,
       @last_engine := engine
from   (select @grp := 1) x,
       (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y
;


| imei            | lat       | lon        | date                | engine | trip|
|-----------------|-----------|------------|---------------------|--------|-----|
| 864251020174383 | 12,137000 | -86,254501 | 04.10.2016 14:15:21 | 1      | 1   |
| 864251020174383 | 12,135400 | -86,253342 | 04.10.2016 14:16:24 | 1      | 1   |
| 864251020174383 | 12,134140 | -86,251671 | 04.10.2016 14:19:12 | 1      | 1   |
| 864251020174383 | 12,134770 | -86,250549 | 04.10.2016 14:19:57 | 1      | 1   |
| 864251020174383 | 12,135820 | -86,249687 | 04.10.2016 14:19:57 | 1      | 1   |
| 864251020174383 | 12,136580 | -86,248581 | 04.10.2016 14:20:02 | 1      | 1   |
| 864251020174383 | 12,137000 | -86,247551 | 04.10.2016 14:20:02 | 1      | 1   |
| 864251020174383 | 12,137160 | -86,246262 | 04.10.2016 14:20:03 | 1      | 1   |
| 864251020174383 | 12,137080 | -86,245621 | 04.10.2016 14:22:33 | 1      | 1   |
| 864251020174383 | 12,136490 | -86,243942 | 04.10.2016 14:23:28 | 1      | 1   |
| 864251020174383 | 12,135990 | -86,243080 | 04.10.2016 14:43:05 | 1      | 1   |
| 864251020174383 | 12,135820 | -86,241798 | 04.10.2016 14:43:57 | 1      | 1   |
| 864251020174383 | 12,135820 | -86,240211 | 04.10.2016 14:47:04 | 1      | 1   |
| 864251020174383 | 12,132720 | -86,237892 | 04.10.2016 14:49:02 | 1      | 1   |
| 864251020174383 | 12,133971 | -86,238281 | 04.10.2016 15:11:27 | 2      | 1   |
| 864251020174383 | 12,104250 | -86,253792 | 11.10.2016 20:01:36 | 1      | 2   |
| 864251020174383 | 12,105340 | -86,251129 | 11.10.2016 20:01:45 | 1      | 2   |
| 864251020174383 | 12,106010 | -86,249069 | 11.10.2016 20:02:02 | 1      | 2   |
| 864251020174383 | 12,102820 | -86,245644 | 11.10.2016 20:02:20 | 1      | 2   |
| 864251020174383 | 12,087050 | -86,231468 | 11.10.2016 20:02:32 | 1      | 2   |
| 864251020174383 | 12,065980 | -86,212334 | 11.10.2016 20:02:48 | 1      | 2   |
| 864251020174383 | 12,065560 | -86,208298 | 11.10.2016 20:02:56 | 1      | 2   |
| 864251020174383 | 12,064720 | -86,205040 | 11.10.2016 20:03:12 | 1      | 2   |
| 864251020174383 | 12,064050 | -86,202888 | 11.10.2016 20:03:20 | 2      | 2   |

然后选择“使用行程号”：

代码语言：javascript
复制
select imei, lat, lon, date, engine
from (select imei, lat, lon, date, engine, 
             if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as grp,
             @last_engine := engine
      from   (select @grp := 0) x,
             (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y) z
where trip = 1
;


| imei            | lat       | lon        | date                | engine |
|-----------------|-----------|------------|---------------------|--------|
| 864251020174383 | 12,137000 | -86,254501 | 04.10.2016 14:15:21 | 1      |
| 864251020174383 | 12,135400 | -86,253342 | 04.10.2016 14:16:24 | 1      |
| 864251020174383 | 12,134140 | -86,251671 | 04.10.2016 14:19:12 | 1      |
| 864251020174383 | 12,134770 | -86,250549 | 04.10.2016 14:19:57 | 1      |
| 864251020174383 | 12,135820 | -86,249687 | 04.10.2016 14:19:57 | 1      |
| 864251020174383 | 12,136580 | -86,248581 | 04.10.2016 14:20:02 | 1      |
| 864251020174383 | 12,137000 | -86,247551 | 04.10.2016 14:20:02 | 1      |
| 864251020174383 | 12,137160 | -86,246262 | 04.10.2016 14:20:03 | 1      |
| 864251020174383 | 12,137080 | -86,245621 | 04.10.2016 14:22:33 | 1      |
| 864251020174383 | 12,136490 | -86,243942 | 04.10.2016 14:23:28 | 1      |
| 864251020174383 | 12,135990 | -86,243080 | 04.10.2016 14:43:05 | 1      |
| 864251020174383 | 12,135820 | -86,241798 | 04.10.2016 14:43:57 | 1      |
| 864251020174383 | 12,135820 | -86,240211 | 04.10.2016 14:47:04 | 1      |
| 864251020174383 | 12,132720 | -86,237892 | 04.10.2016 14:49:02 | 1      |
| 864251020174383 | 12,133971 | -86,238281 | 04.10.2016 15:11:27 | 2      |

就我所知的最终JSON格式而言，我使用了json_array函数。

代码语言：javascript
复制
select json_array(imei, lat, lon, date, engine)
from (select imei, lat, lon, date, engine, 
             if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as trip,
             @last_engine := engine
      from   (select @grp := 0) x,
             (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y) z
where trip = 1
;

| json_array(imei, lat, lon, date, engine)                                    |
|-----------------------------------------------------------------------------|
| ["864251020174383", 12.137000, -86.254501, "2016-10-04 14:15:21.000000", 1] |
| ["864251020174383", 12.135400, -86.253342, "2016-10-04 14:16:24.000000", 1] |
| ["864251020174383", 12.134140, -86.251671, "2016-10-04 14:19:12.000000", 1] |
| ["864251020174383", 12.134770, -86.250549, "2016-10-04 14:19:57.000000", 1] |
| ["864251020174383", 12.135820, -86.249687, "2016-10-04 14:19:57.000000", 1] |
| ["864251020174383", 12.136580, -86.248581, "2016-10-04 14:20:02.000000", 1] |
| ["864251020174383", 12.137000, -86.247551, "2016-10-04 14:20:02.000000", 1] |
| ["864251020174383", 12.137160, -86.246262, "2016-10-04 14:20:03.000000", 1] |
| ["864251020174383", 12.137080, -86.245621, "2016-10-04 14:22:33.000000", 1] |
| ["864251020174383", 12.136490, -86.243942, "2016-10-04 14:23:28.000000", 1] |
| ["864251020174383", 12.135990, -86.243080, "2016-10-04 14:43:05.000000", 1] |
| ["864251020174383", 12.135820, -86.241798, "2016-10-04 14:43:57.000000", 1] |
| ["864251020174383", 12.135820, -86.240211, "2016-10-04 14:47:04.000000", 1] |
| ["864251020174383", 12.132720, -86.237892, "2016-10-04 14:49:02.000000", 1] |
| ["864251020174383", 12.133971, -86.238281, "2016-10-04 15:11:27.000000", 2] |

或者json_object函数：

代码语言：javascript
复制
select json_object('imei',imei,'lat',lat,'lon',lon,'date',date,'engine',engine)
from (select imei, lat, lon, date, engine, 
             if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as trip,
             @last_engine := engine
      from   (select @grp := 0) x,
             (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y) z
where trip = 1
;


| json_object('imei',imei,'lat',lat,'lon',lon,'date',date,'engine',engine)                                            |
|---------------------------------------------------------------------------------------------------------------------|
| {"lat": 12.137000, "lon": -86.254501, "date": "2016-10-04 14:15:21.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135400, "lon": -86.253342, "date": "2016-10-04 14:16:24.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.134140, "lon": -86.251671, "date": "2016-10-04 14:19:12.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.134770, "lon": -86.250549, "date": "2016-10-04 14:19:57.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135820, "lon": -86.249687, "date": "2016-10-04 14:19:57.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.136580, "lon": -86.248581, "date": "2016-10-04 14:20:02.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.137000, "lon": -86.247551, "date": "2016-10-04 14:20:02.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.137160, "lon": -86.246262, "date": "2016-10-04 14:20:03.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.137080, "lon": -86.245621, "date": "2016-10-04 14:22:33.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.136490, "lon": -86.243942, "date": "2016-10-04 14:23:28.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135990, "lon": -86.243080, "date": "2016-10-04 14:43:05.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135820, "lon": -86.241798, "date": "2016-10-04 14:43:57.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135820, "lon": -86.240211, "date": "2016-10-04 14:47:04.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.132720, "lon": -86.237892, "date": "2016-10-04 14:49:02.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.133971, "lon": -86.238281, "date": "2016-10-04 15:11:27.000000", "imei": "864251020174383", "engine": 2} |

在这里查看：http://rextester.com/TGLM4008

票数 0
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://dba.stackexchange.com/questions/152039

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档