查询"current Id"的 MySQL

Question

你好，我正在尝试片刻，以获得每个活动和活动1之间的时差，但我只成功地填写了一个特定的活动，因此获得每一行与此差异。

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    | 
+------+-----------------------------------------------------+ 

因此，我希望在每一行上获得select所选择的活动与活动1之间的时差。有人知道怎么做吗?

这是提要:

+--------+---------------------+------------+--------+
| 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 |
+--------+---------------------+------------+--------+

，这是活动:

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

Answer 1

我一定是在复制源代码....时出错了但这看起来很接近:

select
name,
f1.ts time1,
f4.ts time4,
TIMESTAMPDIFF(SECOND,f1.ts,f4.ts)
from activity a
left join feed f1 on f1.ActivityId = 1 and f1.TeamId=1
left join feed f4 on f4.ActivityId = a.id and f4.TeamId=1
order by name  desc;

看到:DBFIDDLE

输出如下:

<表类>名称time1time4TIMESTAMPDIFF(第二,f1.ts f4.ts)tbody><<tr>act42022-12-20 16:21:302022-12-20 16:39:551105act32022-12-20 16:21:302022-12-20 16:21:300act32022-12-20 16:21:302022-12-20 16:38:541044act22022-12-20 16:21:302022-12-20 16:21:300act12022-12-20 16:21:302022-12-20 16:21:300