teratail
回答率
85.48%
質問するログイン新規登録
トップOracle Databaseに関する質問GROUP BYを使用する際の定義について

編集履歴

質問編集履歴

2

修正の為

2023/02/27 15:25

投稿

k.k
k.k

スコア1

test CHANGED
File without changes
test CHANGED
@@ -29,20 +29,23 @@
29
29
  **SELECT**
30
30
  substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10) as 帳票ID,
31
31
  **CASE**
32
- WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
32
+ WHEN usr.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
33
- WHEN user.BUSYO_CD IN (’064’) THEN ’△’
33
+ WHEN usr.BUSYO_CD IN (’064’) THEN ’△’
34
- WHEN user.BUSYO_CD IN (’065’) THEN ’□’
34
+ WHEN usr.BUSYO_CD IN (’065’) THEN ’□’
35
- WHEN user.BUSYO_CD IN (’066’) THEN ’×’
35
+ WHEN usr.BUSYO_CD IN (’066’) THEN ’×’
36
36
  ELSE code_tiiki.CODE_NAME
37
37
  **END** as 地域,
38
38
  **COUNT**(*) as 回数
39
39
 
40
40
  **FROM**
41
41
  log_table log
42
- **LEFT JOIN** user_table user on log.user_id = user.user_id
42
+ **LEFT JOIN** user_table usr
43
+ on log.user_id = usr.user_id
43
44
  **LEFT JOIN**
44
- (SELECT CODE_KBN, CODE, CODE_NAME FROM code_table
45
+ (SELECT CODE_KBN, CODE, CODE_NAME
46
+ FROM code_table
45
- WHERE CODE_KBN = ’AH’) code_tiiki on use.TIIKI_CD = code.CODE)
47
+ WHERE CODE_KBN = ’AH’) code_tiiki
48
+ on usr.TIIKI_CD = code_tiiki.CODE
46
49
 
47
50
  **WHERE**
48
51
  log.time BETWEEN ‘2022/12/26’ and ‘2023/01/25’
@@ -50,10 +53,10 @@
50
53
  **GROUP BY**
51
54
  substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10),
52
55
  **CASE**
53
- WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
56
+ WHEN usr.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
54
- WHEN user.BUSYO_CD IN (’064’) THEN ’△’
57
+ WHEN usr.BUSYO_CD IN (’064’) THEN ’△’
55
- WHEN user.BUSYO_CD IN (’065’) THEN ’□’
58
+ WHEN usr.BUSYO_CD IN (’065’) THEN ’□’
56
- WHEN user.BUSYO_CD IN (’066’) THEN ’×’
59
+ WHEN usr.BUSYO_CD IN (’066’) THEN ’×’
57
60
  ELSE code_tiiki.CODE_NAME
58
61
  **END**;
59
62
 
@@ -65,11 +68,11 @@
65
68
  **GROUP BY**
66
69
  substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10),
67
70
  **CASE**
68
- WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
71
+ WHEN usr.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
69
- WHEN user.BUSYO_CD IN (’064’) THEN ’△’
72
+ WHEN usr.BUSYO_CD IN (’064’) THEN ’△’
70
- WHEN user.BUSYO_CD IN (’065’) THEN ’□’
73
+ WHEN usr.BUSYO_CD IN (’065’) THEN ’□’
71
- WHEN user.BUSYO_CD IN (’066’) THEN ’×’
74
+ WHEN usr.BUSYO_CD IN (’066’) THEN ’×’
72
- ELSE code_tiiki.CODE_NAME,
75
+ ELSE code_tiiki.CODE_NAME
73
76
  **END**,
74
77
  code_tiiki.CODE_NAME; ← 今回追加した一文
75
78
 
Oracle DatabaseSQL

1

見やすいよう修正

2023/02/27 08:03

投稿

k.k
k.k

スコア1

test CHANGED
File without changes
test CHANGED
@@ -26,50 +26,51 @@
26
26
  Oracle SQL Developer上にて実行
27
27
 
28
28
 
29
- SELECT
29
+ **SELECT**
30
30
  substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10) as 帳票ID,
31
- CASE
31
+ **CASE**
32
+ WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
33
+ WHEN user.BUSYO_CD IN (’064’) THEN ’△’
34
+ WHEN user.BUSYO_CD IN (’065’) THEN ’□’
35
+ WHEN user.BUSYO_CD IN (’066’) THEN ’×’
36
+ ELSE code_tiiki.CODE_NAME
37
+ **END** as 地域,
38
+ **COUNT**(*) as 回数
39
+
40
+ **FROM**
41
+ log_table log
42
+ **LEFT JOIN** user_table user on log.user_id = user.user_id
43
+ **LEFT JOIN**
44
+ (SELECT CODE_KBN, CODE, CODE_NAME FROM code_table
45
+ WHERE CODE_KBN = ’AH’) code_tiiki on use.TIIKI_CD = code.CODE)
46
+
47
+ **WHERE**
48
+ log.time BETWEEN ‘2022/12/26’ and ‘2023/01/25’
49
+
50
+ **GROUP BY**
51
+ substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10),
52
+ **CASE**
32
53
  WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
33
54
  WHEN user.BUSYO_CD IN (’064’) THEN ’△’
34
55
  WHEN user.BUSYO_CD IN (’065’) THEN ’□’
35
56
  WHEN user.BUSYO_CD IN (’066’) THEN ’×’
36
57
  ELSE code_tiiki.CODE_NAME
37
- END as 地域,
38
- COUNT(*) as 回数
39
-
40
- FROM
41
- log_table log
42
- LEFT JOIN user_table user on log.user_id = user.user_id
43
- LEFT JOIN
44
- (SELECT CODE_KBN, CODE, CODE_NAME FROM code_table
45
- WHERE CODE_KBN = ’AH’) code_tiiki on use.TIIKI_CD = code.CODE)
46
-
47
- WHERE
48
- log.time BETWEEN ‘2022/12/26’ and ‘2023/01/25’
49
-
50
- GROUP BY
51
- substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10),
52
- CASE
53
- WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
54
- WHEN user.BUSYO_CD IN (’064’) THEN ’△’
55
- WHEN user.BUSYO_CD IN (’065’) THEN ’□’
56
- WHEN user.BUSYO_CD IN (’066’) THEN ’×’
57
- ELSE code_tiiki.CODE_NAME
58
- END;
58
+ **END**;
59
59
 
60
60
 
61
+
61
- 以下のようにGROUP BY句の中に一行追加したところエラーが解消されたのですが、
62
+ ---以下のようにGROUP BY句の中に一行追加したところエラーが解消されたのですが、
62
- SELECTに直接指定していない一文をGROUP BY に加えてなぜエラーが解消したのかが分かりません、、
63
+ SELECTに直接指定していない一文をGROUP BY に加えてなぜエラーが解消したのかが分かりません、、---
63
64
 
64
- GROUP BY
65
+ **GROUP BY**
65
66
  substr(log.REQUEST_INFO,instr(log.REQUEST_INFO,’cyohyoID:[’) + 10),
66
- CASE
67
+ **CASE**
67
68
  WHEN user.BUSYO_CD IN (’014’, ’023’) THEN ’〇’
68
69
  WHEN user.BUSYO_CD IN (’064’) THEN ’△’
69
70
  WHEN user.BUSYO_CD IN (’065’) THEN ’□’
70
71
  WHEN user.BUSYO_CD IN (’066’) THEN ’×’
71
72
  ELSE code_tiiki.CODE_NAME,
72
- END,
73
+ **END**,
73
74
  code_tiiki.CODE_NAME; ← 今回追加した一文
74
75
 
75
76
 
Oracle DatabaseSQL