根据时间戳和其他条件联接多个表 [英] JOIN Multiple Table based on timestamp and another condition

问题描述

我想加入3张桌子 哪些表A的日期和时间在不同字段中需要先组合在一起，以便可以使用字段时间戳在表B上进行比较.

I want to join 3 tables which Table A have date and time in different field that need to be combined first so it can be compared on Table B using field timestamp.

表A

 userid |  date_in   | check_in
--------+------------+----------
 145    | 2017-01-23 | 08:56:05
 254    | 2017-01-24 | 08:56:54
 202    | 2017-01-25 | 08:53:26
 15     | 2017-01-26 | 08:47:40

表B

 userid |      checktime      |      sn
--------+---------------------+---------------
 145    | 2017-01-23 08:56:05 | 0345135200184
 254    | 2017-01-24 08:56:54 | 0345135200184
 202    | 2017-01-25 08:53:26 | 0345135200184
 15     | 2017-01-26 08:47:40 | 0345135200184

表3

      sn       |    alias
---------------+-------------
 0345135200184 | Alam Sutera

我尝试过:

select process.userid, process.date_in, process.check_in, checkinout.checktime, iclock.alias from process 
inner join checkinout on checkinout.checktime=(select cast (date_in as timestamp(0)) + (check_in - time '00:00:00') checktime from process)
inner join iclock on checkinout.sn=iclock.sn
order by userid desc limit 10;

我也尝试过:

select checkinout.userid, checkinout.checktime, checkinout.sn from checkinout
inner join lateral
(select distinct userid, date_in, check_in from process where 
date_in=(select checktime::date from checkinout) and 
check_in=(select checktime::time from checkinout)
order by date_in asc limit 1)
process ON true;

他们两个都给出错误: 错误:由子查询返回的多行用作表达式

Both of them give error: ERROR: more than one row returned by a subquery used as an expression

预期结果:

 userid |  date_in   | check_in |      checktime     | alias
--------+------------+----------+--------------------+-------
 145    | 2017-01-23 | 08:56:05 | 2017-01-21 09:49:04|Alam Sutera
 254    | 2017-01-24 | 08:56:54 | 2017-01-21 07:57:05|Alam Sutera
 202    | 2017-01-25 | 08:53:26 | 2017-01-21 14:27:00|Alam Sutera
 15     | 2017-01-26 | 08:47:40 | 2017-01-21 06:30:34|Alam Sutera

有人可以帮我解决这个问题吗? 谢谢您的帮助.

Can someone help me to solve this? Thank you for the help.

推荐答案

通过添加date和time字段，您将获得可以比较加入的时间戳.因此您可以像下面这样编写查询:

By adding date and time field you will get the timestamp which you can compare for join. so you can write your query like below:

select 
t1.userid, t1.date_in, t1.check_in, t2.checktime, t3.alias
from process t1
inner join checkinout t2 on t2. checktime= date_in + check_in and t1.userid=t2.userid
inner join table3 t3 on t2.sn=t3.sn

演示

关于您在问题ERROR: more than one row returned by a subquery used as an expression中提到的错误，是由于您使用的联接条件引起的.

Regarding you error you have mentioned in your question ERROR: more than one row returned by a subquery used as an expression is due to join condition you have used.

这篇关于根据时间戳和其他条件联接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！