mysql - INNER JOIN 导致重复行

Question

我正在尝试创建一个查询，以秒为单位显示所有时间差 (TIMEDIFF)。我希望它在两个表之间没有 ID 匹配的情况下发生。这是我的代码，它将显示我正在努力做得更好:

SELECT SUM(TIME_TO_SEC((TIMEDIFF(t.end,t.start)))) 
FROM times AS t 
INNER JOIN changes AS c 
ON t.id != c.shiftid 
WHERE t.deleted=0 AND t.userid=8

次数:

------
Column       Type       Null     Default
------
//**id**//   int(11)    No   
userid       int(11)    No   
start        datetime   No   
end          datetime   No   
deleted      int(11)    No   

id    userID    start                 end                   deleted
 1     39       2014-12-13 12:52:05   2014-12-13 12:52:27   0
 2      8       2014-12-17 08:27:53   2014-12-17 13:29:55   1
 3     13       2014-12-18 16:22:09   2014-12-18 21:06:23   1
 4      8       2014-12-26 09:16:47   2014-12-26 15:34:40   1
 5      8       2012-02-29 09:10:00   2014-12-26 11:39:17   1
 6      8       2014-12-27 14:39:10   2014-12-27 14:40:09   1
 7      8       2014-12-25 14:44:26   2014-12-25 17:45:01   1
 8      8       2014-12-27 14:47:26   2014-12-27 14:48:17   1
 9     13       2014-12-27 16:37:13   2014-12-27 16:37:14   0
10      8       2014-12-27 21:01:58   2014-12-27 21:01:59   1
11     14       2014-12-27 21:02:00   2014-12-27 21:02:04   0
12      9       2014-12-27 21:02:00   2014-12-27 21:02:01   0
13     39       2015-01-02 18:18:41   2015-01-02 18:18:50   0
14      8       2015-02-15 15:13:23   2015-02-15 19:13:25   1
15      8       2015-02-19 14:22:12   2015-02-19 14:22:30   0
16      8       2015-02-19 14:27:01   2015-02-19 14:29:38   0
17      8       2015-02-19 12:33:25   2015-02-19 14:34:36   0
18      8       2015-02-19 14:38:10   2015-02-19 14:52:05   0
19      8       2015-02-19 16:09:07   2015-02-19 16:10:01   0

变化:

------
Column       Type       Null     Default
------
//**id**//   int(11)    No   
shiftid      int(11)    No   
userid       int(11)    No   
start        datetime   No   
end          datetime   No   
deleted      int(11)    No   

ID    shiftID    userID    start                  end                    deleted
 9    17         8         2015-02-19 11:33:25    2015-02-19 14:34:36    0
10    19         8         2015-02-19 16:05:07    2015-02-19 16:10:01    0
12    19         8         2015-02-19 16:04:07    2015-02-19 16:10:01    0

(如果有更好的方式显示表格，请告诉我)编辑:这是格式化表格的正确方法。

在这里，我想显示 times 表中 start 和 end 之间的差值总和，该表的 userid=8 和 deleted=0 但 times.id 不存在于 changes.shiftid

中

Answer 1

我们还可以在这里使用子查询，

select SUM(TIME_TO_SEC((TIMEDIFF(t.end,t.start)))) from times AS t WHERE userid=8 AND deleted=0 AND t.id not in (select shiftID from changes);