MySQL连接表两次而不重复数据

Question

SELECT SEC_TO_TIME(SUM(l1.elapsed)) AS run
FROM pattern
INNER JOIN link l1 ON l1.section = pattern.section
WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'

以上查询为 select 语句中的 run 值返回 00:30:00。我现在需要添加对 link 表的第二个引用。我不会费心去解释为什么，因为它与问题没有直接关系。

SELECT SEC_TO_TIME(SUM(l1.elapsed)) AS run
FROM pattern
INNER JOIN link l1 ON l1.section = pattern.section
INNER JOIN link l2 ON l2.section = pattern.section
WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'

以上是新查询。问题是，我预计 run 仍会返回 00:30:00，因为它只获取 l1 的 SUM ，但现在返回 32:00:00。有人可以解释一下为什么会发生这种情况吗？我需要保留 00:30:00 值，但仍加入该表的第二个副本。

这可能吗？

架构:http://pastebin.com/0vC9pcqz

Answer 1

使用子查询

SELECT r1.run AS run 
FROM ( 
    SELECT pattern.section AS section, SEC_TO_TIME(SUM(l1.elapsed)) AS run
    FROM pattern
    INNER JOIN link l1 ON l1.section = pattern.section
    GROUP BY pattern.service, pattern.direction, pattern.section
    WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'
    ) r1
INNER JOIN link l2 ON l2.section = r1.section

如果您只想获取 1 行，请使用:

SELECT DISTINCT r1.run AS run 
FROM ( 
    SELECT pattern.section AS section, SEC_TO_TIME(SUM(l1.elapsed)) AS run
    FROM pattern
    INNER JOIN link l1 ON l1.section = pattern.section
    GROUP BY pattern.service, pattern.direction, pattern.section
    WHERE pattern.service = "44-A-B-y10-1" AND pattern.direction = 'outbound'
    ) r1
INNER JOIN link l2 ON l2.section = r1.section