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

回答編集履歴

2

回答修正

2016/03/08 23:10

投稿

Aeona
Aeona

スコア396

answer CHANGED
@@ -1,23 +1,34 @@
1
+ 自分のサーバー(MySQL)でテスト環境を作り検証しましたところ、
2
+ いくつか間違いがあったので修正しました。
3
+ 実機動作の確認が出来たので、下記SQLを修正しました。
4
+ (MySQLでは[]が使用できないので``に置換が必要です。)
5
+
6
+ ---
1
7
  こんな感じでしょうか?
2
8
  未検証ですがたぶん動くと思います。
3
9
 
4
10
  ```SQL
5
11
  SELECT
6
- [parent].[group]
12
+ [parent].[col_group]
7
- , [parent].[root_path]
13
+ , [parent].[path] AS root_path
8
- , [parent].[root_size]
14
+ , [parent].[root_size]
9
- , [children].[size_sum]
15
+ , [children].[size_sum]
10
16
  FROM
11
- (SELECT group, root_path, root_size
17
+ (SELECT [T_table].[group] AS col_group
18
+ , [path]
19
+ , [size] AS root_size
12
- FROM T_table
20
+ FROM [t_table]
13
- WHERE path_hierarchy = 0) parent
21
+ WHERE [path_hierarchy] = 0) parent
22
+
14
23
  LEFT JOIN
24
+ (SELECT [t_table].[group] AS col_group
15
- (SELECT group, SUM(size) AS size_sum
25
+ , SUM([size]) AS size_sum
16
- FROM T_table
26
+ FROM [t_table]
17
- WHERE path_hierarchy <> 0
27
+ WHERE [path_hierarchy] <> 0
18
- GROUP BY group) children
28
+ GROUP BY [t_table].[group]) children
19
- ON [parent].[group] = [children].[group]
29
+ ON [parent].[col_group] = [children].[col_group]
20
30
 
31
+ ;
21
32
  ```
22
33
 
23
34
  以上、

1

誤記訂正

2016/03/08 23:10

投稿

Aeona
Aeona

スコア396

answer CHANGED
@@ -12,7 +12,7 @@
12
12
  FROM T_table
13
13
  WHERE path_hierarchy = 0) parent
14
14
  LEFT JOIN
15
- (SELECT group, SUM(size) AS size_sum)
15
+ (SELECT group, SUM(size) AS size_sum
16
16
  FROM T_table
17
17
  WHERE path_hierarchy <> 0
18
18
  GROUP BY group) children