回答編集履歴

9 修正

sazi

sazi score 16748

2018/09/03 11:13  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
```SQL
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select distinct * from step2 as target
where pkey_name<>'' and mail_group<>'' and femail_group<>''
order by all_group
```
```DATA
CREATE TABLE 元データ
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
   
INSERT INTO 元データ
   ([male_name], [groupid1], [female_name], [groupid2])
VALUES
   ('tarou', '001', 'hanako', '101'),
   ('jirou', '002', 'hiroko', '102'),
   ('jirou', '002', 'aiko', '102'),
   ('manabu', '003', 'keiko', '102'),
   ('manabu', '003', 'tomomi', '103'),
   ('naoto', '004', 'tomomi', '103'),
   ('takashi', '003', 'tomomi', '103'),
   ('takashi', '003', 'yumi', '104')
;
```
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
8 編集

sazi

sazi score 16748

2018/09/03 01:24  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select distinct * from step2 as target
where pkey_name<>'' and mail_group<>'' and femail_group<>''
order by top_key
order by all_group
```
```DATA
CREATE TABLE 元データ
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
   
INSERT INTO 元データ
   ([male_name], [groupid1], [female_name], [groupid2])
VALUES
   ('tarou', '001', 'hanako', '101'),
   ('jirou', '002', 'hiroko', '102'),
   ('jirou', '002', 'aiko', '102'),
   ('manabu', '003', 'keiko', '102'),
   ('manabu', '003', 'tomomi', '103'),
   ('naoto', '004', 'tomomi', '103'),
   ('takashi', '003', 'tomomi', '103'),
   ('takashi', '003', 'yumi', '104')
;
```
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
7 編集

sazi

sazi score 16748

2018/09/03 01:19  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select distinct * from step2 as target
where pkey_name<>'' and mail_group<>'' and femail_group<>''  
order by top_key
```
```DATA
CREATE TABLE 元データ
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
   
INSERT INTO 元データ
   ([male_name], [groupid1], [female_name], [groupid2])
VALUES
   ('tarou', '001', 'hanako', '101'),
   ('jirou', '002', 'hiroko', '102'),
   ('jirou', '002', 'aiko', '102'),
   ('manabu', '003', 'keiko', '102'),
   ('manabu', '003', 'tomomi', '103'),
   ('naoto', '004', 'tomomi', '103'),
   ('takashi', '003', 'tomomi', '103'),
   ('takashi', '003', 'yumi', '104')
;
```
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
6 編集

sazi

sazi score 16748

2018/09/03 01:07  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select distinct * from step2 as target
where level=(select max(level) from step2 where top_key=target.top_key)  
order by top_key
```
```DATA
CREATE TABLE 元データ
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
   
INSERT INTO 元データ
   ([male_name], [groupid1], [female_name], [groupid2])
VALUES
   ('tarou', '001', 'hanako', '101'),
   ('jirou', '002', 'hiroko', '102'),
   ('jirou', '002', 'aiko', '102'),
   ('manabu', '003', 'keiko', '102'),
   ('manabu', '003', 'tomomi', '103'),
   ('naoto', '004', 'tomomi', '103'),
   ('takashi', '003', 'tomomi', '103'),
   ('takashi', '003', 'yumi', '104')
;
```
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
5 編集

sazi

sazi score 16748

2018/09/03 00:58  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
    , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
    , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select distinct * from step2 as target
where level=(select max(level) from step2 where top_key=target.top_key)
order by top_key
```
```DATA
CREATE TABLE 元データ
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
   
INSERT INTO 元データ
   ([male_name], [groupid1], [female_name], [groupid2])
VALUES
   ('tarou', '001', 'hanako', '101'),
   ('jirou', '002', 'hiroko', '102'),
   ('jirou', '002', 'aiko', '102'),
   ('manabu', '003', 'keiko', '102'),
   ('manabu', '003', 'tomomi', '103'),
   ('naoto', '004', 'tomomi', '103'),
   ('takashi', '003', 'tomomi', '103'),
   ('takashi', '003', 'yumi', '104')
;
```
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
4 編集

sazi

sazi score 16748

2018/09/03 00:39  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select * from step2 as target
select distinct * from step2 as target
where level=(select max(level) from step2 where top_key=target.top_key)
order by top_key
```
```DATA
CREATE TABLE 元データ
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
   
INSERT INTO 元データ
   ([male_name], [groupid1], [female_name], [groupid2])
VALUES
   ('tarou', '001', 'hanako', '101'),
   ('jirou', '002', 'hiroko', '102'),
   ('jirou', '002', 'aiko', '102'),
   ('manabu', '003', 'keiko', '102'),
   ('manabu', '003', 'tomomi', '103'),
   ('naoto', '004', 'tomomi', '103'),
   ('takashi', '003', 'tomomi', '103'),
   ('takashi', '003', 'yumi', '104')
;
```
再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
3 追記

sazi

sazi score 16748

2018/09/02 22:57  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select * from step2 as target
where level=(select max(level) from step2 where top_key=target.top_key)
order by top_key
```
```DATA  
CREATE TABLE 元データ  
   ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))  
;  
     
INSERT INTO 元データ  
   ([male_name], [groupid1], [female_name], [groupid2])  
VALUES  
   ('tarou', '001', 'hanako', '101'),  
   ('jirou', '002', 'hiroko', '102'),  
   ('jirou', '002', 'aiko', '102'),  
   ('manabu', '003', 'keiko', '102'),  
   ('manabu', '003', 'tomomi', '103'),  
   ('naoto', '004', 'tomomi', '103'),  
   ('takashi', '003', 'tomomi', '103'),  
   ('takashi', '003', 'yumi', '104')  
;  
```  
再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
2 編集

sazi

sazi score 16748

2018/09/02 22:45  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level
select 0 as level, step1.key_name as top_Key
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1
select level+ 1, step2.top_Key
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select * from step2 where level>=1
order by all_group
select * from step2 as target
where level=(select max(level) from step2 where top_key=target.top_key)
order by top_key
```
再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
1 修正

sazi

sazi score 16748

2018/09/02 22:34  投稿

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1:親子関係の状態に正規化
step2:再帰
```
with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ  
union all  
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all  
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ  
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level
    , cast(step1.key_name as varchar(100)) as all_group
    , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
    , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
    , *
from step1
union all
select level+ 1
    , cast(all_group + ' > ' + step1.key_name as varchar(100))
    , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
    , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
    , step1.*
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select * from step2 where level>=1
order by all_group
```
再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る