我有两个表,t1和t2。对于t1中的每个id,我在t2中有多个记录。我想将t2的最接近时间戳与t1的每个记录相匹配。在t1中有一个标志,如果它是1,我想匹配t2的最接近的时间戳,该时间戳较小;如果它是0,我想与大于t1的最接近时间戳相匹配。所以我总共有下表:T1id、标志、时间戳T2id,时间戳
有有效的方法吗?
编辑,这里有一些例子:
T1
customer_id | 时间戳_t1 | 标志|
---|---|---|
1 | 01.01.21 12:00 | 1 |
2 | 01.01.21 13:00 | 0 |
您可以使用相关的子查询来查找时间戳之前/之后的行,然后使用CASE
表达式来选择要加入的行…
SELECT
*
FROM
t1
INNER JOIN
t2
ON t2.id = CASE WHEN t1.flag = 1 THEN
(
SELECT t2.id
FROM t2
WHERE t2.customer_id = t1.customer_id
AND t2.timestamp_t2 <= t1.timestamp_t1
ORDER BY t2.timestamp DESC
LIMIT 1
)
ELSE
(
SELECT t2.id
FROM t2
WHERE t2.customer_id = t1.customer_id
AND t2.timestamp_t2 >= t1.timestamp_t1
ORDER BY t2.timestamp ASC
LIMIT 1
)
END
哦,您的示例中没有包含id
列,这与。。。
SELECT
*
FROM
t1
INNER JOIN
t2
ON t2.customer_id = t1.customer_id
AND t2.timestamp_t2
=
CASE WHEN t1.flag = 1 THEN
(
SELECT MAX(t2.timestamp_t2)
FROM t2
WHERE t2.customer_id = t1.customer_id
AND t2.timestamp_t2 <= t1.timestamp_t1
)
ELSE
(
SELECT MIN(t2.timestamp_t2)
FROM t2
WHERE t2.customer_id = t1.customer_id
AND t2.timestamp_t2 >= t1.timestamp_t1
)
END