回答編集履歴
9
修正
test
CHANGED
|
@@ -6,7 +6,7 @@
|
|
|
6
6
|
|
|
7
7
|
step2：再帰
|
|
8
8
|
|
|
9
|
-
```
|
|
9
|
+
```SQL
|
|
10
10
|
|
|
11
11
|
with step1 as (
|
|
12
12
|
|
8
編集
test
CHANGED
|
@@ -62,7 +62,7 @@
|
|
|
62
62
|
|
|
63
63
|
where pkey_name<>'' and mail_group<>'' and femail_group<>''
|
|
64
64
|
|
|
65
|
-
order by
|
|
65
|
+
order by all_group
|
|
66
66
|
|
|
67
67
|
```
|
|
68
68
|
|
7
編集
test
CHANGED
|
@@ -60,6 +60,8 @@
|
|
|
60
60
|
|
|
61
61
|
select distinct * from step2 as target
|
|
62
62
|
|
|
63
|
+
where pkey_name<>'' and mail_group<>'' and femail_group<>''
|
|
64
|
+
|
|
63
65
|
order by top_key
|
|
64
66
|
|
|
65
67
|
```
|
6
編集
test
CHANGED
|
@@ -60,8 +60,6 @@
|
|
|
60
60
|
|
|
61
61
|
select distinct * from step2 as target
|
|
62
62
|
|
|
63
|
-
where level=(select max(level) from step2 where top_key=target.top_key)
|
|
64
|
-
|
|
65
63
|
order by top_key
|
|
66
64
|
|
|
67
65
|
```
|
5
編集
test
CHANGED
|
@@ -32,9 +32,9 @@
|
|
|
32
32
|
|
|
33
33
|
, cast(step1.key_name as varchar(100)) as all_group
|
|
34
34
|
|
|
35
|
-
, cast(case when type='M' then step1.key_name else '
|
|
35
|
+
, cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
|
|
36
36
|
|
|
37
|
-
, cast(case when type='F' then step1.key_name else '
|
|
37
|
+
, cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
|
|
38
38
|
|
|
39
39
|
, *
|
|
40
40
|
|
|
@@ -46,9 +46,9 @@
|
|
|
46
46
|
|
|
47
47
|
, cast(all_group + ' > ' + step1.key_name as varchar(100))
|
|
48
48
|
|
|
49
|
-
, cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
|
|
49
|
+
, cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
|
|
50
50
|
|
|
51
|
-
, cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
|
|
51
|
+
, cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
|
|
52
52
|
|
|
53
53
|
, step1.*
|
|
54
54
|
|
4
編集
test
CHANGED
|
@@ -58,7 +58,7 @@
|
|
|
58
58
|
|
|
59
59
|
)
|
|
60
60
|
|
|
61
|
-
select * from step2 as target
|
|
61
|
+
select distinct * from step2 as target
|
|
62
62
|
|
|
63
63
|
where level=(select max(level) from step2 where top_key=target.top_key)
|
|
64
64
|
|
|
@@ -102,4 +102,4 @@
|
|
|
102
102
|
|
|
103
103
|
```
|
|
104
104
|
|
|
105
|
-
再帰SQLをカーソルにし
|
|
105
|
+
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
|
3
追記
test
CHANGED
|
@@ -66,4 +66,40 @@
|
|
|
66
66
|
|
|
67
67
|
```
|
|
68
68
|
|
|
69
|
+
```DATA
|
|
70
|
+
|
|
71
|
+
CREATE TABLE 元データ
|
|
72
|
+
|
|
73
|
+
([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
|
|
74
|
+
|
|
75
|
+
;
|
|
76
|
+
|
|
77
|
+
|
|
78
|
+
|
|
79
|
+
INSERT INTO 元データ
|
|
80
|
+
|
|
81
|
+
([male_name], [groupid1], [female_name], [groupid2])
|
|
82
|
+
|
|
83
|
+
VALUES
|
|
84
|
+
|
|
85
|
+
('tarou', '001', 'hanako', '101'),
|
|
86
|
+
|
|
87
|
+
('jirou', '002', 'hiroko', '102'),
|
|
88
|
+
|
|
89
|
+
('jirou', '002', 'aiko', '102'),
|
|
90
|
+
|
|
91
|
+
('manabu', '003', 'keiko', '102'),
|
|
92
|
+
|
|
93
|
+
('manabu', '003', 'tomomi', '103'),
|
|
94
|
+
|
|
95
|
+
('naoto', '004', 'tomomi', '103'),
|
|
96
|
+
|
|
97
|
+
('takashi', '003', 'tomomi', '103'),
|
|
98
|
+
|
|
99
|
+
('takashi', '003', 'yumi', '104')
|
|
100
|
+
|
|
101
|
+
;
|
|
102
|
+
|
|
103
|
+
```
|
|
104
|
+
|
|
69
105
|
再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
|
2
編集
test
CHANGED
|
@@ -28,7 +28,7 @@
|
|
|
28
28
|
|
|
29
29
|
, step2 as(
|
|
30
30
|
|
|
31
|
-
select 0 as level
|
|
31
|
+
select 0 as level, step1.key_name as top_Key
|
|
32
32
|
|
|
33
33
|
, cast(step1.key_name as varchar(100)) as all_group
|
|
34
34
|
|
|
@@ -42,7 +42,7 @@
|
|
|
42
42
|
|
|
43
43
|
union all
|
|
44
44
|
|
|
45
|
-
select level+ 1
|
|
45
|
+
select level+ 1, step2.top_Key
|
|
46
46
|
|
|
47
47
|
, cast(all_group + ' > ' + step1.key_name as varchar(100))
|
|
48
48
|
|
|
@@ -58,9 +58,11 @@
|
|
|
58
58
|
|
|
59
59
|
)
|
|
60
60
|
|
|
61
|
-
select * from step2
|
|
61
|
+
select * from step2 as target
|
|
62
62
|
|
|
63
|
+
where level=(select max(level) from step2 where top_key=target.top_key)
|
|
64
|
+
|
|
63
|
-
order by
|
|
65
|
+
order by top_key
|
|
64
66
|
|
|
65
67
|
```
|
|
66
68
|
|
1
修正
test
CHANGED
|
@@ -10,7 +10,15 @@
|
|
|
10
10
|
|
|
11
11
|
with step1 as (
|
|
12
12
|
|
|
13
|
+
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
|
|
14
|
+
|
|
15
|
+
union all
|
|
16
|
+
|
|
13
17
|
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
|
|
18
|
+
|
|
19
|
+
union all
|
|
20
|
+
|
|
21
|
+
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
|
|
14
22
|
|
|
15
23
|
union all
|
|
16
24
|
|