oracle处理考勤时间,拆分考勤时间段的sql语句

时间:2023-01-13 20:56:19

最近一直在用mysql数据库做云项目,有段时间没有接触oracle了,昨天有朋友叫我帮忙用oracle处理一个考勤记录的需求,我在考虑如何尽量精简实现上面花了一些时间。于是把这个实现做个总结。

需求如下:

  1. rownum为奇数的为进厂时间,偶数的为离场时间
  2. 第一个奇数行的时间被第一个偶数行相减,以此类推
  3. 将上面两步产生的时间结果集相加得出总时间作为当天考勤时间

最终能和别的表联合起来形成这样的展示:

人员

日期

工时

入厂时间

出厂时间

张三

2015/6/18

9小时30分钟

8:00:00

10:30:00

11:00:00

12:30:00

13:30:00

17:30:00

18:00:00

18:30:00

19:00:00

19:30:00

20:00:00

20:30:00

李四

..

..

要我实现的是如何把时间一列的数据拆分成为两列来显示:

原来一列是这样的:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAIcAAADuCAIAAACcfoPPAAALcklEQVR4nO2dPa7yOhCGsyckCtZCQcU60qVlCUiU6FZXok5Dywao6dkBX5HY8c+MY5vEGXLekYtz4rEz9puMY8Ijqvd3dj6fP5/Pl53AOtOTWS0dCYyw6vrf/yjSSvWBybNBlbquF4xj3fZ+v+Od67ru15Xz+dypsmg6XbNFzm0nBFQpZJOpcj1U2g5Xu/Wj2bJ1faV9+HogPK+Hats8uAjN82vr/HXDzsfptzu/6eCYF/H8Nokqj2ZrBn89VMb8+f9Zw3w02+3hsLUP9rPjSRVQJeDmqOKdn9BvYZtEFW8ww4FHsw1O7qPZbpuH63U9VNumcQScRBX3Cng02+pwOKxUFWY0vii2qWl4NFuzh/6wfWNNo0pzNUPqLorrKlXhVw4vYdlmzII1IVbOUe0nUsW8MTtRvBRHrE1lbepnMDWufixhVWgl3q5afQ9TqTLcwL0onsPiNs+TsZ7KYAYjrsze15Wrsq7osI2roqJSoqxTFX8swzLhyTJUUc8I9G3R6dJMpkoXVaNEWacq3mpvScE8GTvru+3qzY71/Bq2GFWsTcpaVXnbyz2zUXNzFDEBauNDzM6j2U6oin1RBFf7JbaRaaqcleETl1ntHG3DvfJ+v5dTRcqHIkJMiCowy6CKRKvruqph8mx4F9m2beabNtik1rYtVBFnUKWoja4onVtQledpp55Sjzd18Has/IPafThkNK6qqtqdnr7f4GNUUx2ZZ2IaRdWSwfMjymzC2/eq3I76lM/Trh+kcdD5043xedpR89J5H29sX44DNR9ko9zayZuE7FtVBiHUf8ebc93ejiOXOB1uP+f2CQgHslurlRtARK0XfHBEOU2Cpmff2TCnZLDB1CVhxUClFv+Cd03dQ+xw3JvMOY1u556eq9V/kcGTBzOaxJl5W/iSpKnyPO0q/1bxJtZTZbfbsYmtu6xPR9aB6bbvOpDRuVoy+JQRRTUJm5OvHElSVLkd9cqZMAYzmZh/q1uoW5W9dYu4x9i1n8jpoVqBqmSs9ios86QJGcwy3W6Yc3s0hAPZrV3tzshobWo6mjODZariSuLGEF7tLTMWSvMGsvOd40B16zwiEJc2Xytqtc9VxUhcH/toTIrQS5HVlTXnxhzqP6nH6ajEGFMr4Mk40lhVjH2SvSuL3UWa20huzfA2JNSDm3sLpm76zPaRW8KMJpNa0U9cuIfleIc/YiVVGU3BaTl6xYZPJyUaVJFobdtWLUyeVa/Pqytt2+q/URYsUEVigSpFy+jePkKVZ71Re6X9TR287Sv/4Of1+rzup011vKh/77X1LnJTP81u9xfbZ3O62+ftHPxuzaiMPj+BSEIx985+h8EmdG24yUSqXPa692e96SM2Dt72lZq7y7BHN1VhZu2279wuR+1vdOs4uN3SAeiS3MQ8cttXu/o+fhaydiSwaVS5nzYqxP6i3t+cq/iyt69W716hI7scdVe0bL0D2a3Vyg2Ab8LGbBXtGW5C1sadRc9+ZVtKBvOvjuFqMqSiVbns6etF3UNWV5QD3e3rcuzTnZumOFXImO0k6V6I4SZkbXBmyHvFlyRNlftp092S3AQxqmw2atkYQnzWm119V1NQH1kHTpWXTlOMJE6TcMzG5T+sKynD7GujzuJlsMp7Fxmtym2fE66Zasy/1ZrRrbHeujU4MLNgXIZ8+k5WxY65mCoZq72+S+wnk9gMZhUd5bBm2BmMcCC7tTXjBj+ewZibrO8w3GSiDJapiivJJ2m1J1Ux1gw7sxMO3IVPaRlSJX61/9zrXbU53Qus9rmqGInLKKHnP3f6zCdOHbp9QfVzqjOYt6hQU0wlxkCT0Zj9OGd7Mo4srCrDg7/eYnUzGLuLpHZn7ppx2TtdeYuK323MZm28iXFpx+4izbshdxf5rSpzFH/NSHX4I6WkKuGcHuPwVwo+nZRYoIrE0uJdpEzDe3txBlUkGlQpaqN7+84tqMovcpFkE/4gO6J5YMnvVflFLpJsQh8MdjjXV8K/VeUnuUg6ktDBwviEnv3KtpQMNtjPcJF0ZP7BhWBJ87bwJUlT5be4SG5WYg7OjeU5+ar6G1wk2SThYGFVMlZ7FcEPcZHBSOjwysKSE6jyc1xkOBJRq32uKr/IRZJNggdlwpKsKr/KRZJN/IOyYcmin7iAi4y0kqqAi4w1fDop0aCKRGvxLlKm4b29uAJVJBaoUrSM7u0jVFkNF0kOxHT2OwQXWY6L9L8wDi5ST1xJLpIeCBUYuMiCXCQ5EHCRVp4tzkWSA3GrwEUW5iLJgdAFXGQ5LpIcCFPARZbiIqMlARf5KsdFBhMXuMhluEh6IOAiTaXBRcaUkqqAi5SoCkpsgSoSS4t3kTIN7+3FGVSRaFClqI3u7Tu3oCpr4SKJ79bSo7S/WM4Mk60FF+k3cmfG/IK8+f1g/pdyjFpwkZwD2e0XXGSwwq4FF8k60N3mc5EfU1PKw8oI4CLDmW8aLrJfPZikateCi2QdmFnI5yLdE/G14CJ5B7LbL7jIcBOrFlwk60B1+w0XSXXA1IKLtP6cgYukIzE9iVpwkWZX83CRRCSGR+wuElzkH7eSqoCLjDV8OinRoIpEa/EuUqbhvb24AlUkFqhStIzu7SNUWQcXaQSsNoseH+N3CC5yZi7SO7upCrhIPXFFfy/S6zz4a2vgIstzkfS8exciuEjOITDF2Vwke6OAi1yMi+RzpuMALpJ3ILvN5SJf1sMFW8BFluMiyWipAi6y3O9FEnLaVx64yEV+L9LZD9nXO7hIcJHiVAEXKVEVlNgCVSSWFu8iZRre24szqCLRoEpRG93bd25BVdbCRY40yfh6KrhIf+hpXOT0X+UGF/k9FxlukoE9gIv0T5PBRRJNMiBHcJGTc5FckwycDlykf9IMLjLUZFlVMlZ7FcGvc5HhJhnpCFyk45jBRY40WWi1z1VlLVzkWJOiT8aRxqqyJi5yGsgRXOQft5KqgIuMNXw6KdGgikRr8S5SpuG9vbgCVSQWqFK0jO7tI1RZBxc51gRcpGq41O9F+k3AReqJK8pFxjVRnuAiS3GRRBNwkVaeXYiLHGkCLrI4FxnVxAwDXCTvQHabwUXGNbFqwUWyDlS3GVxkZBM9m+Aii3CRwSbgIpfjIoOQI7hIcJHiVAEXKVEVlNgCVSSWFu8iZRre24szqCLRoEpRG93bd25BVVbDRVJcHlUb/fVUcJH+0ANfsiaaUN8yp4cJLrIYF0kTGXTv4CJLcZF0OgQXSSa2clyky698i9OBi/RPmsFFGqvN7njcCVIlY7VXEfw6FxmuARe5CBfpVYhY7XNVWQ0XGfEsXezJONJYVdbERRL7TnCR2sBFRlpJVcBFxho+nZRoUEWitXgXKdPw3l5cgSoSC1QpWkb39hGqrIaLHGImvv4LLlI1LMlFGt/4Jn4bB1yknriSXKRPoIGL9LsqzUXS3Be4SCvPluciXX6FoorARS7we5F6Hd7t9zue9QIXWf73IsmY6TjBRbIOVLdf/F6kdTcwqoCLLMZFOk/wfnoEF7kIFzlEoiUHF2kqDS4yppRUBVykRFVQYgtUkVhavIuUaXhvL86gikSDKkVtdG/fuQVV+UUuMjFmthZc5HRcZELMwQ7BRXIOZLdBLjIjZnCR2mt2LnI8ZnCRZGKblYvMjRlc5JxcZGbM4CLn4yKTYgYXWYKLTI0ZXOT8XGR6zOAiZ+ciE2IGF6kNXGSklVQFXGSs4dNJiQZVJFqLd5EyDe/txRWoIrFAlaJldG8focovcpGJMb/ARQ7Rz8VFJsRsTCW4yDm5yIyYvfsMXORsXOR4zOAirTxbhIuMj9m4/CtwkfNykfExe9qAi5yHi0yKmY4TXCTrQHU7ykWmxmwXcJFzcJF5MYOLnJWLTIgZXKQ5a+AiY0pJVcBFSlQFJbZAFYmlxbtImfYP336rhQ47CU0AAAAASUVORK5CYII=" alt="" />

要实现的拆分的效果是这样的:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAARQAAACECAIAAADeA0PZAAANR0lEQVR4nO1dPZLzLBPUnVzlwGdx4MjncObUR3iqHG590Vel2IlTX0Dx5nsDv4EkNMCAAAkEqLsmWIsfDdPbAsky0/wBABCE5ud//4fBYAHWfAEACEJzu9229mFf+Pv7c68MdtIgjJRBPFuvHvcFx4D/+/cP7CSDLykQzzaAeDLEcvH8XJrj/dP/0TSXH7mhKGXxc2l0iN5It2q/n/uR1tSh+lE6lomHhohGhmVHiTzLDovdsbm2eNSB2sVjqWbtto+2SkzNCBfP534k//LyJ7t4LEfs2Aub64rneL9fHK5tOuzhPl4uR9rr535sLpdLgeEORrB4fi7M9Ww8sol46mFzbfF8FLbWEc/9507i/bkfj/ePet66ESoeXTv02DbiqYbN9cUjE7aSeD6fKd59tO1L82Li74pA8XzuRz0W08GNxFMLmzHEM3tt0zEb7j8R7yHa7HnrRV3iqYTNOOLp5SNdUeyYD/cY7zHaZYY7GOmWbZ/7UW2xvnjqYDOWeEb53FcTTx/v+xjtMsMdjDgPDBihcHKLIJ4q2IwnHuURpB0u4Za+Dig03MGI86i6/6g8HdWjGUM8NbAZUzwaVWY4hVtewVtvMbP9Xi0Ucb4k/fsTlzjbzXkU8VTAJl7PKQP/nAF2ksGXlDDxsJeTPK8mxSO+eMCmNzDzlAGwkyEgnjIAdjKEJB4AALwwiKdp8EvsfPF6vbZ2AVAhSIF4sgbEkyEgno0xu7Duq0E8KeFLiiae7nEaH1he2/Fge230g6L6dIg0bpqmOT06vd5UhxRzHdEzGRo5lbLOm0cU2MQTgeIpkR22iTWYxFHSYXzKFoqnvYrTdI/T4Do5qPyp+tU9Tobwtde+Gt+XUoGLAdsotHT1Jt4IEk+J7Pg3oUfa6yifFJQtEs/EyPjp2ioXjWk0ciXxiXdxCL58AqYC263USnXAoVRz3jqikCb+EHw0MiziKZIdYxO3YIqaSSjzJUWZeShGtUrn5WZs/QLFxOD06OyxtbA+tVNPbyoVf7HOswcDmiwAvZ7pJBlmHmXQZbDDNLHHX2kl5tf4lPmSYhRP9zg13KCUCGv0nE7jelVfL/SxeFyNFQzdDl1blq6mUtZ5nxE5NQmAsh5QSJoVT1nsKE2cgjnc9wwlaSjzJcUgnvYa4jedwenf4yWvD4i2ameuicYbVGbxaistRTw6+mq8eEpih2niEczRjU3EM0sKI57ucVKfcrguDCSIdlPw5REwFdhu5WI1CrOlvhN68mWbl3gKY8fUxDmYQ90klC0Vj8qNddx2p8iNG73gycsIpQLXrXIfy1x1zKX5PzDwEk9x7PCeeASTnXqiPzAIEg9ZD3zloy7rIrEQl7qSgk9CLf5k2HFbb7iUhjzEXOe55yqQxFMiO2yTWZ91P3OijBdPO32nNGCIBCmwzeDiNo801Wb9qa9pYHM3+hYH+FLanm27SpP4oOIplR3HJqQG4+dck5SUpXs9hwu+X4U9I/brOWAnAMnEM7vWXOH+oWJEFg/YCQFeDC0DeDE0Q0A8ZQDiyRCSeF4AAPhgEs/v9xeWp71er819gCkG8ZRhEE+GBvFsbLNfZgueNnd1P+ZOikE83e0wfq90bseD7bnRD35/f7+/78ehuT7Hj++b9FvFw62j3Z6fcp3D4y2ft6+gd0u9In1+LZ7YDhpHZB5mYJM1eKqTHXYgtLLeYXx2ForneRan6W6HwXVysD03YxCf03fRlB5D+NpzX+15FfVJt0oFtVveAWFcE/6gtUP7WQKarC6eathhB0L/48dO2nNzur1TsbNIPO/HYfS1j3VzbpWrzvMsD1W7tvEuPq+iK56/oQLbrdRKdcDgie0gPyLrMEOauPHUyLCIpxp2+IGwgRKjS8KOOymz9zyjcCf1M0OV6XmeeX2P1zypK66C4Z/+eR1WEcZlmIt4RDTZEbEHA5p4iufP8KNFwVPF7EgDkVeGU6vepSTsuJMyI57349DPdKZIGeg5HMZF8+Rrdzucbu8xFrersYIl1sPsb+DGRI/LwX5EPsN0auLO0x/3o0XBU8Xs0IGoRcNN0SDaNOy4k2IVT3sO8ZvO4PTvccXc3wgyi92xgiEc5LJhXrwWLR4dgqeK2aED4W10YxPxWEgxiuf9OKhPOVwXBpIJd6cVs7wwYCqw3crkmaLgcc8TMMvHXLZ5iacadtSBGGzoMAk7S8XDDMnjlpSlh6yY5XUtU4HrVr6/XEM8OT0w8BJPNew4Kqf/pz883okfGASJh59GbY/81DjSh4xiDPIFYAiuWBhoS2o11qb1RrB4MnhU7WiSeKphx7pa4/3MiR1ePE/tt4pDKF2/huO+3lJXzM+z0pW2pNa7dfm2y+Npm6lD/WBAk1WNiqcadviBkFC7fkm6ETvpXs/RV8y+FfZssV/PATsBlkw8s2tNv1uFvVlk8YCdEMOLoWUYXgzN0CCeMgziydBe+CUpAARjEo//r7iBRHhhD4P8APGUAYgnQ0A8G2P2y+y+GsSTEr6kaOIhW526bFuqZ5sgIPvlTfWmOpY9ybnt/plGZk8MW7baS3039Z1tYkageOpgx7hxsO5oUnYWiodsiD1t2s1vmK3tavzldwUXHV9bY19KBS4Glh27mSa0mr7rJV+adCvxIPHUwo5+durXduwsEo+8yf14sfBIYtHlkvXSqUAuTZt3RPChXIIt4qmTHXvg0rLjS4oy8yiDaq6tcl57+iTTtuBd6qyXynFzY+kKbvnXZEutkbGDXs90kgwzD0Ul7BjkQQoTsuNLilE8Ik2KKVKcU20mWS/pGIwLcbnUZ5hDqb2JHcp6oNlTTlKloXmqSs2OLykG8bRlZ73UCsxxG0u3FY+OvhovnmrYsVPzTc2OLymMeLpH+VkvDZ7YSu2zfMxlm5d4amLH5TY+JTtLxaNyo57XfkvKDZvO+voywpC5j1ZULk/+4gmYetI9MPAST13suEQsKTvLxEPWA/IgjfOyGkfxQXTVbZD1kveE1mRKUzwM9YUknlrY0dupx/NmhxdPW1HWS8YTUoMpZc9CO2V9cHBsCah4amLHLvTM2Un3eo7p+ah7hT0j9us5YCcAycQzu9b0u1XYGyKLB+yEAC+GlgG8GJohIJ4yAPFkCEk8kX91BwC1YRLP5j8Kh5nshT0M8jOIpwyDeDI0iGdjm/0yW/C0uav7MXdSDOKpMOsl1wQ5STNmBzlJtbFtlfVynX3BkZM0FTvISapY6pyk9iY5pRhpZFjEsxd2qCEn6SZZL5kmyElaBDtKK+Qk3STrpalJhmkVm/3lJJ1pgpyk22W9tDXJUDw6BE97Y0cy5CTdIOulvQlykubMDusncpImy3o50ySnBwZe4tkFO5IhJ2n6rJczTbZ/VO1orypzklqbICdps31O0lVSWCIn6RbsICfpRDmyXgZb7NdzwE6AJRMPsl4ussjiATshhhdDyzC8GJqhQTxlGMSTob3wS1IACMYkHv9fcQOJ8MIeBvkB4ikDEE+GgHg2xuyX2X01iCclfEnRxEO2OnXZtlTPNkFA9sub6k11LHuSc9v9M43MnjAbx0oI2J111Q1dA8WzD3Zct9ulWIOdheIhOwhPG3HzG2Zzw+d2BRcdX1tjX0oFLgaWHbuZJtyW5fwwXfcFX3kr8SDx7IMd2sm0Y2kKdhaJRx7JeLHwSGLRZZL1kk+WwffulpEiWoqRRoZFPHthh22WNsWIIynKzEMxqlU6rz19kmlb8C5x1kt+lSGOsiNiDwY0cQO9nukkGWYeinrZUVqJ+TU+O76kGMUj0qSYIsU51eaS9VJNGrM0C19AEzuU9UCzq5ykVnboCJGTdLrk9QHRVu3MNdF4g2pevBpvSU/X6yl38ejoq/HiqZodrZmWGC6VeGZJYcSjXgs8FgYSRLt245ykWknALB9z2eYlnvrZYf1Mws5S8TCzqMctKTdsOuvzt4raskBfIpv+W0xuqgX5PjDwEs8u2GGLk7CzTDxkPfCVj5rmZTWO4oPoqtsi66XD41OtOMXDUF9I4tkHO7yfObHDi0csRAWGSJAC2wwubvNIU23Wn/qaBqaNy7hEDshJKtikNdgO9YMBTVYFFc9+2GH8ZM+yETvpXs8xPR91r7BnxH49B+wEIJl4ZteafrcKe0Nk8YCdEODF0DKAF0MzBMRTBiCeDCGJJ/Kv7gCgNkzi2fxH4TCTvbCHQX4G8ZRhEE+GBvFsbLNfZgueNnd1P+ZOikE8JWa99PTZWIqcpHmwg5yk2thiZb308NnaIXKS5sEOcpIqFi/rZYDPWaUYaWRYxLMXdqghJ2marJfzPiMnaRHsfOUTISdpgqyXoT5nkVaxqT0nqbvP00nJPQ9yksbNehnocx7i0SF42hs7kiEnaYKsl14+Iydp1uywfiInaaSsl74+Z/XAwEs8u2BHMuQkjZr10t/nHB5VO9qr9JykYT4jJ2marJcePiMnaSHsICfpFD5kvQy22K/ngJ0ASyYeZL1cZJHFA3ZCDC+GlmF4MTRDg3jKMIgnQ3vhl6QAEIxBPP4/4QYA4PuFeAAgGBAPAATiP4Do0I6EP3cjAAAAAElFTkSuQmCC" alt="" />

实现方式如下:

首先创建一个时间表(当然这里我关注的只是scan_time这个字段,其他字段不用理会):

create table CMIS_PERSON_FI_DETAIL_TB
(
detail_id NUMBER(20) not null,
scan_time DATE,
person_fk_id NUMBER(20) not null
)

其次,插入数据,插入数据之后的效果是这样的:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAIcAAADuCAIAAACcfoPPAAALcklEQVR4nO2dPa7yOhCGsyckCtZCQcU60qVlCUiU6FZXok5Dywao6dkBX5HY8c+MY5vEGXLekYtz4rEz9puMY8Ijqvd3dj6fP5/Pl53AOtOTWS0dCYyw6vrf/yjSSvWBybNBlbquF4xj3fZ+v+Od67ru15Xz+dypsmg6XbNFzm0nBFQpZJOpcj1U2g5Xu/Wj2bJ1faV9+HogPK+Hats8uAjN82vr/HXDzsfptzu/6eCYF/H8Nokqj2ZrBn89VMb8+f9Zw3w02+3hsLUP9rPjSRVQJeDmqOKdn9BvYZtEFW8ww4FHsw1O7qPZbpuH63U9VNumcQScRBX3Cng02+pwOKxUFWY0vii2qWl4NFuzh/6wfWNNo0pzNUPqLorrKlXhVw4vYdlmzII1IVbOUe0nUsW8MTtRvBRHrE1lbepnMDWufixhVWgl3q5afQ9TqTLcwL0onsPiNs+TsZ7KYAYjrsze15Wrsq7osI2roqJSoqxTFX8swzLhyTJUUc8I9G3R6dJMpkoXVaNEWacq3mpvScE8GTvru+3qzY71/Bq2GFWsTcpaVXnbyz2zUXNzFDEBauNDzM6j2U6oin1RBFf7JbaRaaqcleETl1ntHG3DvfJ+v5dTRcqHIkJMiCowy6CKRKvruqph8mx4F9m2beabNtik1rYtVBFnUKWoja4onVtQledpp55Sjzd18Has/IPafThkNK6qqtqdnr7f4GNUUx2ZZ2IaRdWSwfMjymzC2/eq3I76lM/Trh+kcdD5043xedpR89J5H29sX44DNR9ko9zayZuE7FtVBiHUf8ebc93ejiOXOB1uP+f2CQgHslurlRtARK0XfHBEOU2Cpmff2TCnZLDB1CVhxUClFv+Cd03dQ+xw3JvMOY1u556eq9V/kcGTBzOaxJl5W/iSpKnyPO0q/1bxJtZTZbfbsYmtu6xPR9aB6bbvOpDRuVoy+JQRRTUJm5OvHElSVLkd9cqZMAYzmZh/q1uoW5W9dYu4x9i1n8jpoVqBqmSs9ios86QJGcwy3W6Yc3s0hAPZrV3tzshobWo6mjODZariSuLGEF7tLTMWSvMGsvOd40B16zwiEJc2Xytqtc9VxUhcH/toTIrQS5HVlTXnxhzqP6nH6ajEGFMr4Mk40lhVjH2SvSuL3UWa20huzfA2JNSDm3sLpm76zPaRW8KMJpNa0U9cuIfleIc/YiVVGU3BaTl6xYZPJyUaVJFobdtWLUyeVa/Pqytt2+q/URYsUEVigSpFy+jePkKVZ71Re6X9TR287Sv/4Of1+rzup011vKh/77X1LnJTP81u9xfbZ3O62+ftHPxuzaiMPj+BSEIx985+h8EmdG24yUSqXPa692e96SM2Dt72lZq7y7BHN1VhZu2279wuR+1vdOs4uN3SAeiS3MQ8cttXu/o+fhaydiSwaVS5nzYqxP6i3t+cq/iyt69W716hI7scdVe0bL0D2a3Vyg2Ab8LGbBXtGW5C1sadRc9+ZVtKBvOvjuFqMqSiVbns6etF3UNWV5QD3e3rcuzTnZumOFXImO0k6V6I4SZkbXBmyHvFlyRNlftp092S3AQxqmw2atkYQnzWm119V1NQH1kHTpWXTlOMJE6TcMzG5T+sKynD7GujzuJlsMp7Fxmtym2fE66Zasy/1ZrRrbHeujU4MLNgXIZ8+k5WxY65mCoZq72+S+wnk9gMZhUd5bBm2BmMcCC7tTXjBj+ewZibrO8w3GSiDJapiivJJ2m1J1Ux1gw7sxMO3IVPaRlSJX61/9zrXbU53Qus9rmqGInLKKHnP3f6zCdOHbp9QfVzqjOYt6hQU0wlxkCT0Zj9OGd7Mo4srCrDg7/eYnUzGLuLpHZn7ppx2TtdeYuK323MZm28iXFpx+4izbshdxf5rSpzFH/NSHX4I6WkKuGcHuPwVwo+nZRYoIrE0uJdpEzDe3txBlUkGlQpaqN7+84tqMovcpFkE/4gO6J5YMnvVflFLpJsQh8MdjjXV8K/VeUnuUg6ktDBwviEnv3KtpQMNtjPcJF0ZP7BhWBJ87bwJUlT5be4SG5WYg7OjeU5+ar6G1wk2SThYGFVMlZ7FcEPcZHBSOjwysKSE6jyc1xkOBJRq32uKr/IRZJNggdlwpKsKr/KRZJN/IOyYcmin7iAi4y0kqqAi4w1fDop0aCKRGvxLlKm4b29uAJVJBaoUrSM7u0jVFkNF0kOxHT2OwQXWY6L9L8wDi5ST1xJLpIeCBUYuMiCXCQ5EHCRVp4tzkWSA3GrwEUW5iLJgdAFXGQ5LpIcCFPARZbiIqMlARf5KsdFBhMXuMhluEh6IOAiTaXBRcaUkqqAi5SoCkpsgSoSS4t3kTIN7+3FGVSRaFClqI3u7Tu3oCpr4SKJ79bSo7S/WM4Mk60FF+k3cmfG/IK8+f1g/pdyjFpwkZwD2e0XXGSwwq4FF8k60N3mc5EfU1PKw8oI4CLDmW8aLrJfPZikateCi2QdmFnI5yLdE/G14CJ5B7LbL7jIcBOrFlwk60B1+w0XSXXA1IKLtP6cgYukIzE9iVpwkWZX83CRRCSGR+wuElzkH7eSqoCLjDV8OinRoIpEa/EuUqbhvb24AlUkFqhStIzu7SNUWQcXaQSsNoseH+N3CC5yZi7SO7upCrhIPXFFfy/S6zz4a2vgIstzkfS8exciuEjOITDF2Vwke6OAi1yMi+RzpuMALpJ3ILvN5SJf1sMFW8BFluMiyWipAi6y3O9FEnLaVx64yEV+L9LZD9nXO7hIcJHiVAEXKVEVlNgCVSSWFu8iZRre24szqCLRoEpRG93bd25BVdbCRY40yfh6KrhIf+hpXOT0X+UGF/k9FxlukoE9gIv0T5PBRRJNMiBHcJGTc5FckwycDlykf9IMLjLUZFlVMlZ7FcGvc5HhJhnpCFyk45jBRY40WWi1z1VlLVzkWJOiT8aRxqqyJi5yGsgRXOQft5KqgIuMNXw6KdGgikRr8S5SpuG9vbgCVSQWqFK0jO7tI1RZBxc51gRcpGq41O9F+k3AReqJK8pFxjVRnuAiS3GRRBNwkVaeXYiLHGkCLrI4FxnVxAwDXCTvQHabwUXGNbFqwUWyDlS3GVxkZBM9m+Aii3CRwSbgIpfjIoOQI7hIcJHiVAEXKVEVlNgCVSSWFu8iZRre24szqCLRoEpRG93bd25BVVbDRVJcHlUb/fVUcJH+0ANfsiaaUN8yp4cJLrIYF0kTGXTv4CJLcZF0OgQXSSa2clyky698i9OBi/RPmsFFGqvN7njcCVIlY7VXEfw6FxmuARe5CBfpVYhY7XNVWQ0XGfEsXezJONJYVdbERRL7TnCR2sBFRlpJVcBFxho+nZRoUEWitXgXKdPw3l5cgSoSC1QpWkb39hGqrIaLHGImvv4LLlI1LMlFGt/4Jn4bB1yknriSXKRPoIGL9LsqzUXS3Be4SCvPluciXX6FoorARS7we5F6Hd7t9zue9QIXWf73IsmY6TjBRbIOVLdf/F6kdTcwqoCLLMZFOk/wfnoEF7kIFzlEoiUHF2kqDS4yppRUBVykRFVQYgtUkVhavIuUaXhvL86gikSDKkVtdG/fuQVV+UUuMjFmthZc5HRcZELMwQ7BRXIOZLdBLjIjZnCR2mt2LnI8ZnCRZGKblYvMjRlc5JxcZGbM4CLn4yKTYgYXWYKLTI0ZXOT8XGR6zOAiZ+ciE2IGF6kNXGSklVQFXGSs4dNJiQZVJFqLd5EyDe/txRWoIrFAlaJldG8focovcpGJMb/ARQ7Rz8VFJsRsTCW4yDm5yIyYvfsMXORsXOR4zOAirTxbhIuMj9m4/CtwkfNykfExe9qAi5yHi0yKmY4TXCTrQHU7ykWmxmwXcJFzcJF5MYOLnJWLTIgZXKQ5a+AiY0pJVcBFSlQFJbZAFYmlxbtImfYP336rhQ47CU0AAAAASUVORK5CYII=" alt="" />

最后编写sql进行实现,实现的sql语句如下所示:

select max(a) in_time, max(b) out_time
from (select decode(mod(rownum, 2), 1, scan_time) a,
decode(mod(rownum, 2), 0, scan_time) b,
round(rownum / 2) c
from CMIS_PERSON_FI_DETAIL_TB)
group by c
order by c;

运行sql,得到了想要的效果,如下所示:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAARQAAACECAIAAADeA0PZAAANR0lEQVR4nO1dPZLzLBPUnVzlwGdx4MjncObUR3iqHG590Vel2IlTX0Dx5nsDv4EkNMCAAAkEqLsmWIsfDdPbAsky0/wBABCE5ud//4fBYAHWfAEACEJzu9229mFf+Pv7c68MdtIgjJRBPFuvHvcFx4D/+/cP7CSDLykQzzaAeDLEcvH8XJrj/dP/0TSXH7mhKGXxc2l0iN5It2q/n/uR1tSh+lE6lomHhohGhmVHiTzLDovdsbm2eNSB2sVjqWbtto+2SkzNCBfP534k//LyJ7t4LEfs2Aub64rneL9fHK5tOuzhPl4uR9rr535sLpdLgeEORrB4fi7M9Ww8sol46mFzbfF8FLbWEc/9507i/bkfj/ePet66ESoeXTv02DbiqYbN9cUjE7aSeD6fKd59tO1L82Li74pA8XzuRz0W08GNxFMLmzHEM3tt0zEb7j8R7yHa7HnrRV3iqYTNOOLp5SNdUeyYD/cY7zHaZYY7GOmWbZ/7UW2xvnjqYDOWeEb53FcTTx/v+xjtMsMdjDgPDBihcHKLIJ4q2IwnHuURpB0u4Za+Dig03MGI86i6/6g8HdWjGUM8NbAZUzwaVWY4hVtewVtvMbP9Xi0Ucb4k/fsTlzjbzXkU8VTAJl7PKQP/nAF2ksGXlDDxsJeTPK8mxSO+eMCmNzDzlAGwkyEgnjIAdjKEJB4AALwwiKdp8EvsfPF6vbZ2AVAhSIF4sgbEkyEgno0xu7Duq0E8KeFLiiae7nEaH1he2/Fge230g6L6dIg0bpqmOT06vd5UhxRzHdEzGRo5lbLOm0cU2MQTgeIpkR22iTWYxFHSYXzKFoqnvYrTdI/T4Do5qPyp+tU9Tobwtde+Gt+XUoGLAdsotHT1Jt4IEk+J7Pg3oUfa6yifFJQtEs/EyPjp2ioXjWk0ciXxiXdxCL58AqYC263USnXAoVRz3jqikCb+EHw0MiziKZIdYxO3YIqaSSjzJUWZeShGtUrn5WZs/QLFxOD06OyxtbA+tVNPbyoVf7HOswcDmiwAvZ7pJBlmHmXQZbDDNLHHX2kl5tf4lPmSYhRP9zg13KCUCGv0nE7jelVfL/SxeFyNFQzdDl1blq6mUtZ5nxE5NQmAsh5QSJoVT1nsKE2cgjnc9wwlaSjzJcUgnvYa4jedwenf4yWvD4i2ameuicYbVGbxaistRTw6+mq8eEpih2niEczRjU3EM0sKI57ucVKfcrguDCSIdlPw5REwFdhu5WI1CrOlvhN68mWbl3gKY8fUxDmYQ90klC0Vj8qNddx2p8iNG73gycsIpQLXrXIfy1x1zKX5PzDwEk9x7PCeeASTnXqiPzAIEg9ZD3zloy7rIrEQl7qSgk9CLf5k2HFbb7iUhjzEXOe55yqQxFMiO2yTWZ91P3OijBdPO32nNGCIBCmwzeDiNo801Wb9qa9pYHM3+hYH+FLanm27SpP4oOIplR3HJqQG4+dck5SUpXs9hwu+X4U9I/brOWAnAMnEM7vWXOH+oWJEFg/YCQFeDC0DeDE0Q0A8ZQDiyRCSeF4AAPhgEs/v9xeWp71er819gCkG8ZRhEE+GBvFsbLNfZgueNnd1P+ZOikE83e0wfq90bseD7bnRD35/f7+/78ehuT7Hj++b9FvFw62j3Z6fcp3D4y2ft6+gd0u9In1+LZ7YDhpHZB5mYJM1eKqTHXYgtLLeYXx2ForneRan6W6HwXVysD03YxCf03fRlB5D+NpzX+15FfVJt0oFtVveAWFcE/6gtUP7WQKarC6eathhB0L/48dO2nNzur1TsbNIPO/HYfS1j3VzbpWrzvMsD1W7tvEuPq+iK56/oQLbrdRKdcDgie0gPyLrMEOauPHUyLCIpxp2+IGwgRKjS8KOOymz9zyjcCf1M0OV6XmeeX2P1zypK66C4Z/+eR1WEcZlmIt4RDTZEbEHA5p4iufP8KNFwVPF7EgDkVeGU6vepSTsuJMyI57349DPdKZIGeg5HMZF8+Rrdzucbu8xFrersYIl1sPsb+DGRI/LwX5EPsN0auLO0x/3o0XBU8Xs0IGoRcNN0SDaNOy4k2IVT3sO8ZvO4PTvccXc3wgyi92xgiEc5LJhXrwWLR4dgqeK2aED4W10YxPxWEgxiuf9OKhPOVwXBpIJd6cVs7wwYCqw3crkmaLgcc8TMMvHXLZ5iacadtSBGGzoMAk7S8XDDMnjlpSlh6yY5XUtU4HrVr6/XEM8OT0w8BJPNew4Kqf/pz883okfGASJh59GbY/81DjSh4xiDPIFYAiuWBhoS2o11qb1RrB4MnhU7WiSeKphx7pa4/3MiR1ePE/tt4pDKF2/huO+3lJXzM+z0pW2pNa7dfm2y+Npm6lD/WBAk1WNiqcadviBkFC7fkm6ETvpXs/RV8y+FfZssV/PATsBlkw8s2tNv1uFvVlk8YCdEMOLoWUYXgzN0CCeMgziydBe+CUpAARjEo//r7iBRHhhD4P8APGUAYgnQ0A8G2P2y+y+GsSTEr6kaOIhW526bFuqZ5sgIPvlTfWmOpY9ybnt/plGZk8MW7baS3039Z1tYkageOpgx7hxsO5oUnYWiodsiD1t2s1vmK3tavzldwUXHV9bY19KBS4Glh27mSa0mr7rJV+adCvxIPHUwo5+durXduwsEo+8yf14sfBIYtHlkvXSqUAuTZt3RPChXIIt4qmTHXvg0rLjS4oy8yiDaq6tcl57+iTTtuBd6qyXynFzY+kKbvnXZEutkbGDXs90kgwzD0Ul7BjkQQoTsuNLilE8Ik2KKVKcU20mWS/pGIwLcbnUZ5hDqb2JHcp6oNlTTlKloXmqSs2OLykG8bRlZ73UCsxxG0u3FY+OvhovnmrYsVPzTc2OLymMeLpH+VkvDZ7YSu2zfMxlm5d4amLH5TY+JTtLxaNyo57XfkvKDZvO+voywpC5j1ZULk/+4gmYetI9MPAST13suEQsKTvLxEPWA/IgjfOyGkfxQXTVbZD1kveE1mRKUzwM9YUknlrY0dupx/NmhxdPW1HWS8YTUoMpZc9CO2V9cHBsCah4amLHLvTM2Un3eo7p+ah7hT0j9us5YCcAycQzu9b0u1XYGyKLB+yEAC+GlgG8GJohIJ4yAPFkCEk8kX91BwC1YRLP5j8Kh5nshT0M8jOIpwyDeDI0iGdjm/0yW/C0uav7MXdSDOKpMOsl1wQ5STNmBzlJtbFtlfVynX3BkZM0FTvISapY6pyk9iY5pRhpZFjEsxd2qCEn6SZZL5kmyElaBDtKK+Qk3STrpalJhmkVm/3lJJ1pgpyk22W9tDXJUDw6BE97Y0cy5CTdIOulvQlykubMDusncpImy3o50ySnBwZe4tkFO5IhJ2n6rJczTbZ/VO1orypzklqbICdps31O0lVSWCIn6RbsICfpRDmyXgZb7NdzwE6AJRMPsl4ussjiATshhhdDyzC8GJqhQTxlGMSTob3wS1IACMYkHv9fcQOJ8MIeBvkB4ikDEE+GgHg2xuyX2X01iCclfEnRxEO2OnXZtlTPNkFA9sub6k11LHuSc9v9M43MnjAbx0oI2J111Q1dA8WzD3Zct9ulWIOdheIhOwhPG3HzG2Zzw+d2BRcdX1tjX0oFLgaWHbuZJtyW5fwwXfcFX3kr8SDx7IMd2sm0Y2kKdhaJRx7JeLHwSGLRZZL1kk+WwffulpEiWoqRRoZFPHthh22WNsWIIynKzEMxqlU6rz19kmlb8C5x1kt+lSGOsiNiDwY0cQO9nukkGWYeinrZUVqJ+TU+O76kGMUj0qSYIsU51eaS9VJNGrM0C19AEzuU9UCzq5ykVnboCJGTdLrk9QHRVu3MNdF4g2pevBpvSU/X6yl38ejoq/HiqZodrZmWGC6VeGZJYcSjXgs8FgYSRLt245ykWknALB9z2eYlnvrZYf1Mws5S8TCzqMctKTdsOuvzt4raskBfIpv+W0xuqgX5PjDwEs8u2GGLk7CzTDxkPfCVj5rmZTWO4oPoqtsi66XD41OtOMXDUF9I4tkHO7yfObHDi0csRAWGSJAC2wwubvNIU23Wn/qaBqaNy7hEDshJKtikNdgO9YMBTVYFFc9+2GH8ZM+yETvpXs8xPR91r7BnxH49B+wEIJl4ZteafrcKe0Nk8YCdEODF0DKAF0MzBMRTBiCeDCGJJ/Kv7gCgNkzi2fxH4TCTvbCHQX4G8ZRhEE+GBvFsbLNfZgueNnd1P+ZOikE8JWa99PTZWIqcpHmwg5yk2thiZb308NnaIXKS5sEOcpIqFi/rZYDPWaUYaWRYxLMXdqghJ2marJfzPiMnaRHsfOUTISdpgqyXoT5nkVaxqT0nqbvP00nJPQ9yksbNehnocx7i0SF42hs7kiEnaYKsl14+Iydp1uywfiInaaSsl74+Z/XAwEs8u2BHMuQkjZr10t/nHB5VO9qr9JykYT4jJ2marJcePiMnaSHsICfpFD5kvQy22K/ngJ0ASyYeZL1cZJHFA3ZCDC+GlmF4MTRDg3jKMIgnQ3vhl6QAEIxBPP4/4QYA4PuFeAAgGBAPAATiP4Do0I6EP3cjAAAAAElFTkSuQmCC" alt="" />