teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問【MySQL】月単位での使用件数を表示させたい。

編集履歴

回答編集履歴

2

良い方を上に

2015/11/18 03:01

投稿

anonymouskawa
anonymouskawa

スコア856

test CHANGED
@@ -1,3 +1,43 @@
1
+ ```SQL
2
+
3
+ SELECT
4
+
5
+ `akusesutaipu` AS 'テスト',
6
+
7
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '12ヶ月前',
8
+
9
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '11ヶ月前',
10
+
11
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '10ヶ月前',
12
+
13
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '9ヶ月前',
14
+
15
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '8ヶ月前',
16
+
17
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '7ヶ月前',
18
+
19
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '6ヶ月前',
20
+
21
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '5ヶ月前',
22
+
23
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '4ヶ月前',
24
+
25
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '3ヶ月前',
26
+
27
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '2ヶ月前',
28
+
29
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '1ヶ月前',
30
+
31
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
32
+
33
+ FROM access_record
34
+
35
+ GROUP BY `akusesutaipu`
36
+
37
+ ```
38
+
39
+ もしくは
40
+
1
41
  ```SQL
2
42
 
3
43
  SELECT
@@ -36,48 +76,6 @@
36
76
 
37
77
  ```
38
78
 
39
-
40
-
41
- もしくは
42
-
43
- ```SQL
44
-
45
- SELECT
46
-
47
- `akusesutaipu` AS 'テスト',
48
-
49
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '12ヶ月前',
50
-
51
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '11ヶ月前',
52
-
53
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '10ヶ月前',
54
-
55
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '9ヶ月前',
56
-
57
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '8ヶ月前',
58
-
59
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '7ヶ月前',
60
-
61
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '6ヶ月前',
62
-
63
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '5ヶ月前',
64
-
65
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '4ヶ月前',
66
-
67
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '3ヶ月前',
68
-
69
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '2ヶ月前',
70
-
71
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '1ヶ月前',
72
-
73
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
74
-
75
- FROM access_record
76
-
77
- GROUP BY `akusesutaipu`
78
-
79
- ```
80
-
81
79
  ご指摘ありがとうございます！
82
80
 
83
81
 

1

指摘事項反映

2015/11/18 03:01

投稿

anonymouskawa
anonymouskawa

スコア856

test CHANGED
@@ -4,29 +4,29 @@
4
4
 
5
5
  `akusesutaipu` AS 'テスト',
6
6
 
7
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '12ヶ月前',
7
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '12ヶ月前',
8
8
 
9
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '11ヶ月前',
9
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '11ヶ月前',
10
10
 
11
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '10ヶ月前',
11
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '10ヶ月前',
12
12
 
13
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '9ヶ月前',
13
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '9ヶ月前',
14
14
 
15
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '8ヶ月前',
15
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '8ヶ月前',
16
16
 
17
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '7ヶ月前',
17
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '7ヶ月前',
18
18
 
19
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '6ヶ月前',
19
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '6ヶ月前',
20
20
 
21
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '5ヶ月前',
21
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '5ヶ月前',
22
22
 
23
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '4ヶ月前',
23
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '4ヶ月前',
24
24
 
25
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '3ヶ月前',
25
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '3ヶ月前',
26
26
 
27
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '2ヶ月前',
27
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '2ヶ月前',
28
28
 
29
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '1ヶ月前',
29
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_ADD(now(),INTERVAL -1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '1ヶ月前',
30
30
 
31
31
  COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
32
32
 
@@ -38,4 +38,48 @@
38
38
 
39
39
 
40
40
 
41
+ もしくは
42
+
43
+ ```SQL
44
+
45
+ SELECT
46
+
47
+ `akusesutaipu` AS 'テスト',
48
+
49
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '12ヶ月前',
50
+
51
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '11ヶ月前',
52
+
53
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '10ヶ月前',
54
+
55
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '9ヶ月前',
56
+
57
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '8ヶ月前',
58
+
59
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '7ヶ月前',
60
+
61
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '6ヶ月前',
62
+
63
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '5ヶ月前',
64
+
65
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '4ヶ月前',
66
+
67
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '3ヶ月前',
68
+
69
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '2ヶ月前',
70
+
71
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS '1ヶ月前',
72
+
73
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
74
+
75
+ FROM access_record
76
+
77
+ GROUP BY `akusesutaipu`
78
+
79
+ ```
80
+
81
+ ご指摘ありがとうございます！
82
+
83
+
84
+
41
85
  動的に列別名つけるのってどうしたらいいんだろう。