mysql - 按顺序连接表

Question

让我说，我有两个表 PTO_CONTROL 和 RUTA_PTO，我尝试仅通过它们的 FK_PTO 加入此表按提供 PTO_CONTROL 表的顺序共享。到目前为止的问题是总是获得第一注册在搜索中找到的内容，获得重复的值。如何创建 sql 指令来解决此问题？谢谢。

TABLE PTO_CONTROLID  FK_PTO  NUM     HORA55  122     566     5:02        56  104     568     5:1657  114     572     5:3858  104     573     6:1259  110     577     6:2860  122     582     6:45TABLE RUTA_PTOID  FK_RUTA FK_PTO  TIEMPO15  56      122     016  56      104     1217  56      114     2218  56      104     3219  56      121     4020  56      110     4521  56      122     55JOIN TABLE / VALUES EXPECTEDFK_PTO  NUM HORA TIEMPO122     566 5:02 0104     568 5:16 12114     572 5:38 22104     573 6:12 32110     577 6:28 45122     582 6:45 55

DML和sql指令可以是，

    CREATE TABLE pto (     ID INT PRIMARY KEY,     NOMBRE VARCHAR(32)    );    CREATE TABLE ruta (     ID INT PRIMARY KEY,     NOMBRE VARCHAR(32)    );    CREATE TABLE ruta_pto (     ID INT PRIMARY KEY,     FK_RUTA INT NOT NULL,     FK_PTO INT NOT NULL,     TIEMPO INT,     CONSTRAINT FK_RUTA_PTO_PTO      FOREIGN KEY (FK_PTO)      REFERENCES pto (ID)     CONSTRAINT FK_RUTA_PTO_RUTA      FOREIGN KEY (FK_RUTA)      REFERENCES ruta (ID)    );    CREATE TABLE pto_control (     ID INT PRIMARY KEY,     FK_PTO INT NOT NULL,     NUM INT,     HORA TIME,     CONSTRAINT FK_PTO_CONTROL_PTO      FOREIGN KEY (FK_PTO)      REFERENCES pto (ID)    );    SELECT     pc.FK_PTO,     pc.NUM,     pc.HORA,     rp.TIEMPO    FROM pto_control AS pc    INNER JOIN ruta_pto AS rp ON     pc.FK_PTO = rp.FK_PTO AND rp.FK_RUTA = 56    INNER JOIN pto AS p ON     pc.FK_PTO = p.ID;    and the wrong result that obtain is    FK_PTO  NUM HORA TIEMPO    122     566 5:02 0    104     568 5:16 12    114     572 5:38 22    104     568 5:16 12    110     577 6:28 45    122     566 5:02 0

Answer 1

您需要对两个表实现分组排名。这可以通过以下查询来完成:

select c1.*, count(*) as rank
from PTO_CONTROL c1
join PTO_CONTROL c2
  on  c2.FK_PTO = c1.FK_PTO
  and c2.ID <= c1.ID
group by c1.ID;

结果:

| ID | FK_PTO | NUM | HORA | rank |
|----|--------|-----|------|------|
| 55 |    122 | 566 | 5:02 |    1 |
| 56 |    104 | 568 | 5:16 |    1 |
| 57 |    114 | 572 | 5:38 |    1 |
| 58 |    104 | 573 | 6:12 |    2 |
| 59 |    110 | 577 | 6:28 |    1 |
| 60 |    122 | 582 | 6:45 |    2 |

还有

select r1.*, count(*) as rank
from RUTA r1
join RUTA r2
  on  r2.FK_PTO = r1.FK_PTO
  and r2.ID <= r1.ID
group by r1.ID;

结果:

| ID | FK_RUTA | FK_PTO | TIEMPO | rank |
|----|---------|--------|--------|------|
| 15 |      56 |    122 |      0 |    1 |
| 16 |      56 |    104 |     12 |    1 |
| 17 |      56 |    114 |     22 |    1 |
| 18 |      56 |    104 |     32 |    2 |
| 19 |      56 |    121 |     40 |    1 |
| 20 |      56 |    110 |     45 |    1 |
| 21 |      56 |    122 |     55 |    2 |

现在您可以使用生成的排名连接两个结果

select *
from (
  select c1.*, count(*) as rank
  from PTO_CONTROL c1
  join PTO_CONTROL c2
    on  c2.FK_PTO = c1.FK_PTO
    and c2.ID <= c1.ID
  group by c1.ID
) c
join (
  select r1.*, count(*) as rank
  from RUTA r1
  join RUTA r2
    on  r2.FK_PTO = r1.FK_PTO
    and r2.ID <= r1.ID
  group by r1.ID
) r using (FK_PTO, rank)

结果:

| FK_PTO | NUM | HORA | TIEMPO |
|--------|-----|------|--------|
|    122 | 566 | 5:02 |      0 |
|    104 | 568 | 5:16 |     12 |
|    114 | 572 | 5:38 |     22 |
|    104 | 573 | 6:12 |     32 |
|    110 | 577 | 6:28 |     45 |
|    122 | 582 | 6:45 |     55 |

http://sqlfiddle.com/#!9/f3f427/5