如何基于第一个表中的非唯一id连接两个表(SQLite 3.12.2)

Question

表A的Serial ID是随机生成的。Table B将根据Table A的序列号和类别生成Table A的声音。

我尝试了这个命令，但是失败了。

SELECT          A.timestamp, B.timestamp, B.SerialID, A.Category, B.Sound
FROM            A 
JOIN            B
ON              B.timestamp > A.timestamp
AND             A.SerialID = B.SerialID
ORDER BY        B.timestamp

表

tbody> <<tr>21013

时间戳	SerialID	类别
1	3	猫
	5	狗
	44	猫
	5	猫
15	3	狗

SELECT
t1.Timestamp AS TimestampA,
t2.Timestamp AS TimestampB,
t1.SerialID,
t1.Category,
t2.Sound
FROM t1
JOIN t2 ON t1.SerialID = t2.SerialID AND t2.Timestamp = (
SELECT MIN(t2.Timestamp)
FROM t2 
WHERE t2.SerialID = t1.SerialID AND t2.Timestamp >= t1.Timestamp
)

或者同样使用LEAD窗函数

WITH cte AS (
SELECT *,
LEAD(Timestamp) OVER (PARTITION BY SerialID ORDER BY Timestamp) as next_timestamp
FROM t1
)
SELECT
t1.Timestamp AS TimestampA,
t2.Timestamp AS TimestampB,
t1.SerialID,
t1.Category,
t2.Sound
FROM cte t1
JOIN t2 ON t1.SerialID = t2.SerialID 
AND t2.Timestamp >= t1.Timestamp 
AND (t2.Timestamp < t1.next_timestamp OR next_timestamp IS NULL)
ORDER BY t1.Timestamp  

,db&lt的在小提琴

我最喜欢的解决方案是横向连接，只是为每个a行选择所需的B行。但是横向连接在SQLite中还没有出现。

通过在select子句的子查询中选择时间戳，您可以通过两个步骤获得所需的B行:

select
ab.timestamp as a_timestamp, b.timestamp as b_timestamp,
b.serialid, ab.category, b.sound
from
(
select a.*,
(
select b.timestamp
from b
where b.serialid = a.serialid
and b.timestamp > a.timestamp
order by b.timestamp
limit 1
) as best_timestamp
from a
) ab
join b on b.serialid = ab.serialid
and b.timestamp = ab.best_timestamp
order by ab.timestamp, ab.serialid;

另一种方法使用窗口函数。在那里，你会加入所有的候选人，然后留下最好的那个。这是添加了拾取的查询。

select a_timestamp, b_timestamp, serialid, category, sound
from
(
select 
a.timestamp as a_timestamp, b.timestamp as b_timestamp,
b.serialid, a.category, b.sound,
min(b.timestamp) over (partition by a.serialid) as best_timestamp
from a
join b on b.serialid = a.serialid and b.timestamp > a.timestamp
) ab
where b_timestamp = best_timestamp
order by a_timestamp, ab.serialid;

你所需要的只是一个连接和聚合，没有子查询:

SELECT a.Timestamp a_Timestamp,
MIN(b.Timestamp) b_Timestamp,
a.SerialID,
a.Category,
b.Sound
FROM TableA a INNER JOIN TableB b
ON b.SerialID = a.SerialID AND b.Timestamp > a.Timestamp
GROUP BY a.Timestamp, a.SerialID;

这段代码依赖于SQLite的特性，当使用MIN()

聚合函数时，返回包含一列最小值的行。如果Timestamp和SerialID的组合在TableA中不是唯一的，则改为:

GROUP BY a.Timestamp, a.SerialID, a.Category