teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問MySQLで同じテーブルで日付毎の所要時間を一覧で出したい

編集履歴

回答編集履歴

3

修正

2017/08/04 03:33

投稿

SVC34
SVC34

スコア1149

test CHANGED
@@ -24,55 +24,55 @@
24
24
 
25
25
  SELECT
26
26
 
27
- create_date
27
+ create_date
28
28
 
29
- ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
29
+ ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
30
30
 
31
- ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
31
+ ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
32
32
 
33
- ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
33
+ ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
34
34
 
35
- ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
35
+ ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
36
36
 
37
- ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
37
+ ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
38
38
 
39
- ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
39
+ ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
40
40
 
41
41
  FROM
42
42
 
43
- (
43
+ (
44
44
 
45
- SELECT
45
+ SELECT
46
46
 
47
- t1.user_id
47
+ t1.user_id
48
48
 
49
- ,DATE(t1.create_datetime) AS create_date
49
+ ,DATE(t1.create_datetime) AS create_date
50
50
 
51
- ,t1.laps
51
+ ,t1.laps
52
52
 
53
- ,t1.chk_point AS chk1
53
+ ,t1.chk_point AS chk1
54
54
 
55
- ,t2.chk_point AS chk2
55
+ ,t2.chk_point AS chk2
56
56
 
57
- ,TIMESTAMPDIFF(SECOND, t1.create_datetime, t2.create_datetime) AS diff
57
+ ,TIMESTAMPDIFF(SECOND, t1.create_datetime, t2.create_datetime) AS diff
58
58
 
59
- FROM
59
+ FROM
60
60
 
61
- result_time AS t1
61
+ result_time AS t1
62
62
 
63
- INNER JOIN result_time AS t2
63
+ INNER JOIN result_time AS t2
64
64
 
65
- ON t1.user_id = t2.user_id
65
+ ON t1.user_id = t2.user_id
66
66
 
67
- AND t1.laps = t2.laps
67
+ AND t1.laps = t2.laps
68
68
 
69
- AND t1.chk_point + 1 = t2.chk_point
69
+ AND t1.chk_point + 1 = t2.chk_point
70
70
 
71
- ) AS tmp
71
+ ) AS tmp
72
72
 
73
73
  GROUP BY
74
74
 
75
- create_date
75
+ create_date
76
76
 
77
77
  ;
78
78
 

2

修正

2017/08/04 03:33

投稿

SVC34
SVC34

スコア1149

test CHANGED
@@ -26,17 +26,17 @@
26
26
 
27
27
  create_date
28
28
 
29
- ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)), '%i:%s')
29
+ ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
30
30
 
31
- ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)), '%i:%s')
31
+ ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
32
32
 
33
- ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)), '%i:%s')
33
+ ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s')
34
34
 
35
- ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)), '%i:%s')
35
+ ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
36
36
 
37
- ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)), '%i:%s')
37
+ ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
38
38
 
39
- ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)), '%i:%s')
39
+ ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s')
40
40
 
41
41
  FROM
42
42
 

1

修正

2017/08/04 03:31

投稿

SVC34
SVC34

スコア1149

test CHANGED
@@ -4,7 +4,7 @@
4
4
 
5
5
  サブクエリで以下を求める
6
6
 
7
- - user_id, laps, 日付が一致し、さらにchk_pointの差が1のレコード同士を結合する
7
+ - user_id, lapsが一致し、さらにchk_pointの差が1のレコード同士を結合する
8
8
 
9
9
  - タイムスタンプの差分を求める
10
10
 
@@ -26,17 +26,17 @@
26
26
 
27
27
  create_date
28
28
 
29
- ,MAX(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)
29
+ ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)), '%i:%s')
30
30
 
31
- ,MIN(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)
31
+ ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)), '%i:%s')
32
32
 
33
- ,AVG(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)
33
+ ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 0 AND chk2 = 1 THEN diff ELSE NULL END)), '%i:%s')
34
34
 
35
- ,MAX(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)
35
+ ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)), '%i:%s')
36
36
 
37
- ,MIN(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)
37
+ ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)), '%i:%s')
38
38
 
39
- ,AVG(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)
39
+ ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 1 AND chk2 = 2 THEN diff ELSE NULL END)), '%i:%s')
40
40
 
41
41
  FROM
42
42
 
@@ -54,7 +54,7 @@
54
54
 
55
55
  ,t2.chk_point AS chk2
56
56
 
57
- ,TIMESTAMPDIFF(MINUTE, t1.create_datetime, t2.create_datetime) AS diff
57
+ ,TIMESTAMPDIFF(SECOND, t1.create_datetime, t2.create_datetime) AS diff
58
58
 
59
59
  FROM
60
60
 
@@ -63,8 +63,6 @@
63
63
  INNER JOIN result_time AS t2
64
64
 
65
65
  ON t1.user_id = t2.user_id
66
-
67
- AND DATE(t1.create_datetime) = DATE(t2.create_datetime)
68
66
 
69
67
  AND t1.laps = t2.laps
70
68