Re: [SQL ] 請問如何相減再 group 起來?
※ 引述《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://www.ptt.cc/bbs/Database/M.1441848113.A.ACA.html
推
09/13 09:05, , 1F
09/13 09:05, 1F
討論串 (同標題文章)
本文引述了以下文章的的內容：
完整討論串 (本文為第 2 之 2 篇)：