MySQL查询：如何计算各活动与活动1的时间差

我尝试获取每个活动与活动1的时间差，但当前编写的SQL仅能得到活动4与活动1的固定差值，所有行结果均相同。

现有错误SQL #

select 
  name, 
  TIMESTAMPDIFF(SECOND, 
                (select ts 
                         from feed 
                         where TeamId=1 and ActivityId=1), 
                 (select ts 
                    from feed 
                    where TeamId=1 and ActivityId=4)) 
from activity 
inner join feed on feed.ActivityId = activity.id 
where TeamId=1 order by FeedId DESC;

错误执行结果 #

+------+-----------------------------------------------------+ 
| act4 |                                                1105 | 
| act3 |                                                1105 | 
| act3 |                                                1105 | 
| act2 |                                                1105 | 
| act1 |                                                1105 |
+------+-----------------------------------------------------+ 

期望结果 #

+------+-----------------------------------------------------+ 
| name |                                       TIMESTAMPDIFF |
+------+-----------------------------------------------------+ 
| act4 |                                                1105 | 
| act3 |                                                11   | 
| act3 |                                                11   | 
| act2 |                                                1    | 
| act1 |                                                0    | 
+------+-----------------------------------------------------+ 

即每行显示当前选中活动与活动1的时间差。

相关表结构 #

feed表 #

+--------+---------------------+------------+--------+
| FeedId | ts                  | ActivityId | TeamId |
+--------+---------------------+------------+--------+
|      1 | 2022-12-20 16:21:30 |          1 |      1 |
|      2 | 2022-12-20 16:21:30 |          1 |      2 |
|      3 | 2022-12-20 16:21:30 |          1 |      3 |
|      4 | 2022-12-20 16:21:30 |          2 |      1 |
|      5 | 2022-12-20 16:21:30 |          3 |      1 |
|      6 | 2022-12-20 16:21:30 |          2 |      2 |
|      7 | 2022-12-20 16:38:54 |          3 |      1 |
|      8 | 2022-12-20 16:39:55 |          4 |      1 |
+--------+---------------------+------------+--------+

activity表 #

+----+--------------+------+-------+
| id | localisation | name | point |
+----+--------------+------+-------+
|  1 | Madras       | act1 |  -650 |
|  2 | Valparaiso   | act2 |   450 |
|  3 | Amphi        | act3 |    45 |
|  4 | Amphix       | act4 |  4589 |
+----+--------------+------+-------+

解决方案 #

错误原因 #

原SQL中第二个子查询固定取了ActivityId=4的时间戳，导致所有行都用这个固定值和活动1的时间计算差值，结果自然全部相同。需要替换为当前行对应的活动时间戳。

修正后的SQL（方法一：子查询基准时间） #

SELECT 
    a.name,
    TIMESTAMPDIFF(SECOND, 
                  (SELECT ts FROM feed WHERE TeamId=1 AND ActivityId=1), 
                  f.ts) AS TIMESTAMPDIFF
FROM activity a
INNER JOIN feed f ON f.ActivityId = a.id
WHERE f.TeamId = 1 
ORDER BY f.FeedId DESC;

修正后的SQL（方法二：关联基准时间表，效率更高） #

如果数据量较大，推荐用CROSS JOIN预先获取活动1的基准时间，避免子查询重复执行：

SELECT 
    a.name,
    TIMESTAMPDIFF(SECOND, base.ts, f.ts) AS TIMESTAMPDIFF
FROM activity a
INNER JOIN feed f ON f.ActivityId = a.id
CROSS JOIN (SELECT ts FROM feed WHERE TeamId=1 AND ActivityId=1) base
WHERE f.TeamId = 1 
ORDER BY f.FeedId DESC;

逻辑说明 #

1. 先获取TeamId=1的活动1的时间戳作为基准值
2. 每行使用当前feed记录的ts（对应该行活动的时间）与基准值计算秒级时间差
3. 保留原有的关联和排序逻辑，确保结果顺序符合预期

内容的提问来源于stack exchange，提问作者Ne Mo