teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

2

良い方を上に

2015/11/18 03:01

投稿

anonymouskawa
anonymouskawa

スコア856

answer CHANGED
@@ -1,6 +1,26 @@
1
1
  ```SQL
2
2
  SELECT
3
3
  `akusesutaipu` AS 'テスト',
4
+ 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ヶ月前',
5
+ 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ヶ月前',
6
+ 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ヶ月前',
7
+ 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ヶ月前',
8
+ 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ヶ月前',
9
+ 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ヶ月前',
10
+ 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ヶ月前',
11
+ 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ヶ月前',
12
+ 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ヶ月前',
13
+ 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ヶ月前',
14
+ 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ヶ月前',
15
+ 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ヶ月前',
16
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
17
+ FROM access_record
18
+ GROUP BY `akusesutaipu`
19
+ ```
20
+ もしくは
21
+ ```SQL
22
+ SELECT
23
+ `akusesutaipu` AS 'テスト',
4
24
  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ヶ月前',
5
25
  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ヶ月前',
6
26
  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ヶ月前',
@@ -17,27 +37,6 @@
17
37
  FROM access_record
18
38
  GROUP BY `akusesutaipu`
19
39
  ```
20
-
21
- もしくは
22
- ```SQL
23
- SELECT
24
- `akusesutaipu` AS 'テスト',
25
- 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ヶ月前',
26
- 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ヶ月前',
27
- 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ヶ月前',
28
- 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ヶ月前',
29
- 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ヶ月前',
30
- 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ヶ月前',
31
- 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ヶ月前',
32
- 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ヶ月前',
33
- 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ヶ月前',
34
- 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ヶ月前',
35
- 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ヶ月前',
36
- 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ヶ月前',
37
- COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
38
- FROM access_record
39
- GROUP BY `akusesutaipu`
40
- ```
41
40
  ご指摘ありがとうございます!
42
41
 
43
42
  動的に列別名つけるのってどうしたらいいんだろう。

1

指摘事項反映

2015/11/18 03:01

投稿

anonymouskawa
anonymouskawa

スコア856

answer CHANGED
@@ -1,21 +1,43 @@
1
1
  ```SQL
2
2
  SELECT
3
3
  `akusesutaipu` AS 'テスト',
4
- 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ヶ月前',
4
+ 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ヶ月前',
5
- 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ヶ月前',
5
+ 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ヶ月前',
6
- 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ヶ月前',
6
+ 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ヶ月前',
7
- 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ヶ月前',
7
+ 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ヶ月前',
8
- 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ヶ月前',
8
+ 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ヶ月前',
9
- 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ヶ月前',
9
+ 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ヶ月前',
10
- 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ヶ月前',
10
+ 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ヶ月前',
11
- 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ヶ月前',
11
+ 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ヶ月前',
12
- 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ヶ月前',
12
+ 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ヶ月前',
13
- 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ヶ月前',
13
+ 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ヶ月前',
14
- 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ヶ月前',
14
+ 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ヶ月前',
15
- 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ヶ月前',
15
+ 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ヶ月前',
16
16
  COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
17
17
  FROM access_record
18
18
  GROUP BY `akusesutaipu`
19
19
  ```
20
20
 
21
+ もしくは
22
+ ```SQL
23
+ SELECT
24
+ `akusesutaipu` AS 'テスト',
25
+ 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ヶ月前',
26
+ 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ヶ月前',
27
+ 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ヶ月前',
28
+ 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ヶ月前',
29
+ 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ヶ月前',
30
+ 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ヶ月前',
31
+ 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ヶ月前',
32
+ 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ヶ月前',
33
+ 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ヶ月前',
34
+ 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ヶ月前',
35
+ 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ヶ月前',
36
+ 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ヶ月前',
37
+ COUNT(CASE WHEN date_format(akusesujikoku, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS '今月'
38
+ FROM access_record
39
+ GROUP BY `akusesutaipu`
40
+ ```
41
+ ご指摘ありがとうございます!
42
+
21
43
  動的に列別名つけるのってどうしたらいいんだろう。