作者YaMeiLo (亚妹露~!!)
看板Database
标题Re: [SQL ] 请问如何相减再 group 起来?
时间Thu Sep 10 09:21:47 2015
※ 引述《fantasywater (prepare myself)》之铭言：
: +-------+-------+--------+---------+
: | att1 | att2 | in/our | time |
: +-------+-------+--------+---------+
: | A | B | in | 7:00 |
: +-------+-------+--------+---------+
: | C | D | out | 7:50 |
: +-------+-------+--------+---------+
: | A | B | out | 7:20 |
: +-------+-------+--------+---------+
: | C | D | in | 7:10 |
: +-------+-------+--------+---------+
: 请问如何下 SQL 计算时间差再 group 起来呢..?
: 结果希望像下面这样..苦手中..
: +-------+-------+-------------------+
: | att1 | att2 | time_diff |
: +-------+-------+-------------------+
: | A | B | 10 |
: +-------+-------+-------------------+
: | C | D | 40 |
: +-------+-------+-------------------+
分两次查询再 JOIN 起来相减
SELECT tb_in.att1 ,tb_in.att2
,DATEDIFF(S ,tb_in.[time] ,tb_out.[time]) / 60 time_diff
FROM (
SELECT att1 ,att2 ,[time] FROM dbo.tb WHERE [in/out] = 'in'
) AS tb_in INNER JOIN (
SELECT att1 ,att2 ,[time] FROM dbo.tb WHERE [in/out] = 'out'
) AS tb_out ON tb_in.att1 = tb_out.att1 AND tb_in.att2 = tb_out.att2
如果你的 att1 & att2 会有多次，就要再加工一下。
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 42.71.95.102
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1441848113.A.ACA.html
1F：推 fantasywater: 可以了 感谢!! 09/13 09:05