回答編集履歴

9 修正

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 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 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 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 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 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 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 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 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をカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。