MySQL - 链接表问题(选择唯一行)

Question

我一直试图解决这个问题，但失败了。我有下面的问题快照，它已被删除。我想要的是以下内容:

action_id   event_id    link    text    duration
339054      10221877    317547  text1   30
339057      10221877    317548  text2   45
339061      10221877    317549  text3   30
339065      10221877    317551  text4   40
339074      10221877    317550  text5   60
339075      10221877    317552  text6   80

仅用两张 table 就可以做到吗？如果没有，什么可能有帮助？

CREATE TABLE time1
(

prim int (5),
booking int (5) PRIMARY KEY,
duration int (5));

insert into time1 values 
( 10221877, 296480,30),
( 10221877, 296484,45),
( 10221877, 296487,30),
( 10221877, 296492,40),
( 10221877, 296494,60),
( 10221877, 296510,80),
( 10221877, 296511,11);


CREATE TABLE action1
(

action_id int (5) PRIMARY KEY,
event_id int (5),
link int (5),
text_text VARCHAR(255)

);

insert into action1 values 
(339054,10221877,317547,"text1"),
(339057,10221877,317548,"text2"),
(339061,10221877,317549,"text3"),
(339065,10221877,317551,"text4"),
(339074,10221877,317550,"text5"),
(339075,10221877,317552,"text6");

Answer 1

如我所见，您想要将 action1 表的第一行连接到 time1 表的第一行，将 action1 表的第二行连接到 time1 的第二行，依此类推。所以你可以使用 variables计算行的序数并通过它连接表。例如:

SELECT a.action_id, a.event_id, a.link, a.text_text, t.duration
FROM (SELECT action_id,
             event_id,
             link,
             text_text,
             IF(@event_id = event_id, @rownumber := @rownumber + 1, @rownumber := 0) as rownumber,
             @event_id := event_id
      FROM action1,
           (SELECT @event_id := 0, @rownumber := 0) AS rn) as a
         LEFT JOIN
     (SELECT duration,
             prim,
             IF(@prim = prim, @rownumber := @rownumber + 1, @rownumber := 0) as rownumber,
             @prim := prim
      FROM time1,
           (SELECT @prim := 0, @rownumber := 0) AS rn) as t ON a.event_id = t.prim AND a.rownumber = t.rownumber

结果是

--------------------------------------------------------
| action_id | event_id | link   | text_text | duration |
--------------------------------------------------------
|    339054 | 10221877 | 317547 | text1     |       30 |
|    339057 | 10221877 | 317548 | text2     |       45 |
|    339061 | 10221877 | 317549 | text3     |       30 |
|    339065 | 10221877 | 317551 | text4     |       40 |
|    339074 | 10221877 | 317550 | text5     |       60 |
|    339075 | 10221877 | 317552 | text6     |       80 |
--------------------------------------------------------

演示在 DBFiddle