使用具有相同id part2的select将选定的列值转换为另一行 [英] Convert selected column values to another row using select with the same id part2

问题描述

我正在制作一个简单的超时和超时系统.我有3对输入和输出. p_id(person_id)

I am making a simple time in and time out system.. i have 3 pairs of in and outs. p_id(person_id)

TableA

p_id  time_id      status            timestamp
1         1           in        2013-12-18 15:44:09
2         2           in        2013-12-18 16:23:19
1         3           out       2013-12-18 18:31:11
1         4           in        2013-12-18 18:50:11
3         5           out       2013-12-18 19:20:16
1         6           out       2013-12-18 19:50:11
2         7           out       2013-12-18 19:51:19
1         8           in        2013-12-19 07:51:19
1         9           out       2013-12-19 12:00:19
1         10          in        2013-12-19 01:00:19
1         11          out       2013-12-19 05:30:19
1         12          in        2013-12-19 07:51:19
1         13          out       2013-12-19 11:00:19

进入表结果同一日期的一行

Into Table Result one row for the same date

id status     timestamp        status     timestamp       status     timestamp          status      timestamp          status     timestamp       status      timestamp
1    in   2013-12-18 15:44:09   out   2013-12-18 18:31:11  in    2013-12-18 18:50:11    out     2013-12-18 19:50:11
2    in   2013-12-18 16:23:19   out   2013-12-18 19:51:19  
3                               out   2013-12-18 19:20:16
1    in   2013-12-19 07:51:19   out   2013-12-19 12:00:19  in    2013-12-19 01:00:19    out     2013-12-19 05:30:19     in    2013-12-19 07:51:19   out       2013-12-19 11:00:19

推荐答案

我想您想要这样的东西:

I guess you want something like this:

SELECT a1.id, a.1status, a1.timestamp, a2.status, a2.timetstamp
FROM timetable a1
LEFT JOIN timetable a2 ON a2.id=a1.id AND a2.status = 'out'
WHERE a1.status = 'in'

只有一个超值的行对我来说没有任何意义.

To have a row with only an out-value doesn't make any sense to me.

这篇关于使用具有相同id part2的select将选定的列值转换为另一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！