teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップSQL Serverに関する質問SQL Serverで配列的な集計ができるクエリを作成したい

編集履歴

回答編集履歴

9

修正

2018/09/03 02:13

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -2,7 +2,7 @@
2
2
  例えば以下のようなSQLで経路を辿ることはできます。
3
3
  step1：親子関係の状態に正規化
4
4
  step2：再帰
5
- ```
5
+ ```SQL
6
6
  with step1 as (
7
7
  select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
8
8
  union all

8

編集

2018/09/03 02:13

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -30,7 +30,7 @@
30
30
  )
31
31
  select distinct * from step2 as target
32
32
  where pkey_name<>'' and mail_group<>'' and femail_group<>''
33
- order by top_key
33
+ order by all_group
34
34
  ```
35
35
  ```DATA
36
36
  CREATE TABLE 元データ

7

編集

2018/09/02 16:24

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -29,6 +29,7 @@
29
29
  where step2.pkey_name<>step1.key_name
30
30
  )
31
31
  select distinct * from step2 as target
32
+ where pkey_name<>'' and mail_group<>'' and femail_group<>''
32
33
  order by top_key
33
34
  ```
34
35
  ```DATA

6

編集

2018/09/02 16:19

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -29,7 +29,6 @@
29
29
  where step2.pkey_name<>step1.key_name
30
30
  )
31
31
  select distinct * from step2 as target
32
- where level=(select max(level) from step2 where top_key=target.top_key)
33
32
  order by top_key
34
33
  ```
35
34
  ```DATA

5

編集

2018/09/02 16:07

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -15,15 +15,15 @@
15
15
  , step2 as(
16
16
  select 0 as level, step1.key_name as top_Key
17
17
  , cast(step1.key_name as varchar(100)) as all_group
18
- , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
18
+ , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
19
- , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
19
+ , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
20
20
  , *
21
21
  from step1
22
22
  union all
23
23
  select level+ 1, step2.top_Key
24
24
  , cast(all_group + ' > ' + step1.key_name as varchar(100))
25
- , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
25
+ , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
26
- , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
26
+ , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100))
27
27
  , step1.*
28
28
  from step2 inner join step1 on step2.key_name=step1.pkey_name
29
29
  where step2.pkey_name<>step1.key_name

4

編集

2018/09/02 15:58

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -28,7 +28,7 @@
28
28
  from step2 inner join step1 on step2.key_name=step1.pkey_name
29
29
  where step2.pkey_name<>step1.key_name
30
30
  )
31
- select * from step2 as target
31
+ select distinct * from step2 as target
32
32
  where level=(select max(level) from step2 where top_key=target.top_key)
33
33
  order by top_key
34
34
  ```
@@ -50,4 +50,4 @@
50
50
  ('takashi', '003', 'yumi', '104')
51
51
  ;
52
52
  ```
53
- 再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。
53
+ 上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。

3

追記

2018/09/02 15:39

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -32,4 +32,22 @@
32
32
  where level=(select max(level) from step2 where top_key=target.top_key)
33
33
  order by top_key
34
34
  ```
35
+ ```DATA
36
+ CREATE TABLE 元データ
37
+ ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
38
+ ;
39
+
40
+ INSERT INTO 元データ
41
+ ([male_name], [groupid1], [female_name], [groupid2])
42
+ VALUES
43
+ ('tarou', '001', 'hanako', '101'),
44
+ ('jirou', '002', 'hiroko', '102'),
45
+ ('jirou', '002', 'aiko', '102'),
46
+ ('manabu', '003', 'keiko', '102'),
47
+ ('manabu', '003', 'tomomi', '103'),
48
+ ('naoto', '004', 'tomomi', '103'),
49
+ ('takashi', '003', 'tomomi', '103'),
50
+ ('takashi', '003', 'yumi', '104')
51
+ ;
52
+ ```
35
53
  再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。

2

編集

2018/09/02 13:57

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -13,14 +13,14 @@
13
13
  select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
14
14
  )
15
15
  , step2 as(
16
- select 0 as level
16
+ select 0 as level, step1.key_name as top_Key
17
17
  , cast(step1.key_name as varchar(100)) as all_group
18
18
  , cast(case when type='M' then step1.key_name else ',' end as varchar(100)) as mail_group
19
19
  , cast(case when type='F' then step1.key_name else ',' end as varchar(100)) as femail_group
20
20
  , *
21
21
  from step1
22
22
  union all
23
- select level+ 1
23
+ select level+ 1, step2.top_Key
24
24
  , cast(all_group + ' > ' + step1.key_name as varchar(100))
25
25
  , cast(mail_group + case when step1.type='M' then ',' + step1.key_name else '' end as varchar(100))
26
26
  , cast(femail_group + case when step1.type='F' then ',' + step1.key_name else '' end as varchar(100))
@@ -28,7 +28,8 @@
28
28
  from step2 inner join step1 on step2.key_name=step1.pkey_name
29
29
  where step2.pkey_name<>step1.key_name
30
30
  )
31
- select * from step2 where level>=1
31
+ select * from step2 as target
32
+ where level=(select max(level) from step2 where top_key=target.top_key)
32
- order by all_group
33
+ order by top_key
33
34
  ```
34
35
  再帰SQLをカーソルにして、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。

1

修正

2018/09/02 13:45

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -4,8 +4,12 @@
4
4
  step2：再帰
5
5
  ```
6
6
  with step1 as (
7
+ select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
8
+ union all
7
9
  select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
8
10
  union all
11
+ select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
12
+ union all
9
13
  select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
10
14
  )
11
15
  , step2 as(