I have searched on the *, but I didn't find any question mentioning to merge two records in single row based on different date.
我搜索了*，但是我没有发现任何问题提及要根据不同的日期将两个记录合并到一行中。
I have this table.
我有这个表。
ID Timestamp State
6 2016-02-03 07:13 End
5 2016-02-02 21:09 Start
4 2016-02-02 9:10 End
3 2016-02-01 21:10 Start
2 2016-02-01 6:30 End
1 2016-01-31 23:40 Start
So process 'A' is starting at 2016-01-31 23:40 and getting stopped at 2016-02-01 6:30, and so on. I want to display it in this way:
因此，进程“A”在2016-01-31 23:40开始，在2016-02-01 6:30停止，以此类推。我想这样展示:
Start Time End Time
2016-01-31 23:40 2016-02-01 6:30
2016-02-01 21:10 2016-02-02 9:10
2016-02-02 21:09 2016-02-03 07:13
There are other records between Start and End tags. Can you tell me how to go solve it? I am trying to get the solution on the WAMP server.
开始和结束标记之间还有其他记录。你能告诉我怎么去解决吗?我正在尝试在WAMP服务器上获得解决方案。
2 个解决方案
#1
2
If less query, and better performance is your thing...
如果查询越少，性能越好……
SELECT x.timestamp start
, MIN(y.timestamp) end
FROM my_table x
JOIN my_table y
ON y.timestamp > x.timestamp
AND y.state = 'end'
WHERE x.state = 'start'
GROUP
BY x.timestamp;
#2
2
If the ID has no gaps, you could just use a self-join on id <=> id+1, but it is usually a bad idea to rely on such things with technical IDs. You could do about the same by giving row numbers to all starts and all ends and join on these. Or you use a simple subselect in your where clause:
如果ID没有空格，您可以只在ID <=> ID +1上使用self-join，但是在技术ID上依赖这些东西通常是一个坏主意。你可以通过给所有的开始和结束和连接来做同样的事情。或者在where子句中使用一个简单的子选择:
select
timestamp as start_time,
(
select min(timestamp)
from mytable ends
where ends.state = 'End'
and ends.timestamp > starts.timestamp
) as end_time
from mytable starts
where state = 'Start'
order by timestamp;
#1
2
If less query, and better performance is your thing...
如果查询越少，性能越好……
SELECT x.timestamp start
, MIN(y.timestamp) end
FROM my_table x
JOIN my_table y
ON y.timestamp > x.timestamp
AND y.state = 'end'
WHERE x.state = 'start'
GROUP
BY x.timestamp;
#2
2
If the ID has no gaps, you could just use a self-join on id <=> id+1, but it is usually a bad idea to rely on such things with technical IDs. You could do about the same by giving row numbers to all starts and all ends and join on these. Or you use a simple subselect in your where clause:
如果ID没有空格，您可以只在ID <=> ID +1上使用self-join，但是在技术ID上依赖这些东西通常是一个坏主意。你可以通过给所有的开始和结束和连接来做同样的事情。或者在where子句中使用一个简单的子选择:
select
timestamp as start_time,
(
select min(timestamp)
from mytable ends
where ends.state = 'End'
and ends.timestamp > starts.timestamp
) as end_time
from mytable starts
where state = 'Start'
order by timestamp;