Microsoft Access Club Access超初心者対象Forum Access初・中級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトル兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No82998
投稿日: 2016/10/16(Sun) 17:05
投稿者kamijo
解決済: ON
OS: Windows 10 Pro
Access Version: 2016 (2010も使用)


No16860.SQLで件数を指定する方法
と
SELECT [電話番号] FROM [T_児童マスタ] GROUP BY [電話番号] HAVING Count(*)>1

上記2つ等を元に、次のテーブルに適応させようとしていますが、
スマートな文、クエリ(または他の手法)が思いつきません。
どうかアドバイスをお願いいたします。


T_児童マスタ

学級 氏名 電話番号
11  A  1111-11-1111
21  B    2222-22-2222
31  C    1111-11-1111
41  D    2222-22-2222
51  E    1111-11-1111
61  F    3333-33-3333

↓

望む出力結果

学級 氏名 電話番号    兄弟学級1 兄弟氏名1 兄弟学級2 兄弟氏名2 兄弟学級3 兄弟氏名3
11  A  1111-11-1111  31     C     51     E     
21  B    2222-22-2222  41     E
31  C    1111-11-1111  11     A     51     E
41  D    2222-22-2222  21     B
51  E    1111-11-1111  11     A     31     C
61  F    3333-33-3333

タイトルRe: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83000
投稿日: 2016/10/17(Mon) 13:21
投稿者hatena
解決済: ON
Access SQLでは LIMIT はないので、代替手段として TOP を使うことになりますが、
サブクエリをつかう複雑なSQLになりますね。

さらにクエリだけで希望の出力結果にしようとするとさらに複雑になりそうなのでちょっと避けたいですね。

最終目的が印刷なら、レポート側で工夫するほうがシンプルにできそうです。

下記のクエリを作成します。

SELECT T_児童マスタ.*, T.学級 AS 兄弟学級, T.氏名 AS 兄弟氏名
FROM T_児童マスタ LEFT JOIN T_児童マスタ AS T
ON T_児童マスタ.電話番号 = T.電話番号 AND T.氏名<>T_児童マスタ.氏名
ORDER BY T_児童マスタ.学級, T.学級;

このクエリの出力結果は、

学級	氏名	電話番号	兄弟学級	兄弟氏名
11	A	1111-11-1111	31	C
11	A	1111-11-1111	51	E
21	B	2222-22-2222	41	D
31	C	1111-11-1111	11	A
31	C	1111-11-1111	51	E
41	D	2222-22-2222	21	B
51	E	1111-11-1111	11	A
51	E	1111-11-1111	31	C
61	F	3333-33-3333		


これをレポートのレコードソースにして、
学級、氏名 でグループ化して、氏名ヘッダーに 学級	氏名	電話番号 を配置、
詳細セクションに 兄弟学級	兄弟氏名 を配置

あとは、詳細セクションを横に展開する方法は下記で紹介している方法を使えば、
希望のレイアウトのレポートが完成します。

グループ化したレコードを横に展開する - hatena chips
http://hatenachips.blog34.fc2.com/blog-entry-105.html

タイトルRe^2: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83001
投稿日: 2016/10/17(Mon) 18:27
投稿者kamijo
解決済: ON
ありがとうございます。
今出先なので、これから戻って実行してみたいと思います。

タイトルRe^2: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83003
投稿日: 2016/10/18(Tue) 06:19
投稿者kamijo
解決済: ON
出力結果をさらに利用したいとも考えておりますので、
できるだけクエリで処理しようとしています。

今、以下のように書いてみましたが、複数のJOINの使用によるエラーで躓いております。



SELECT MM.ID, MM.入学年度, MM.学年, MM.[クラス], MM.[クラス内番号],
MM.姓, MM.名, MM.[せい], MM.[めい], 
MM.性別, MM.生年月日, MM.電話番号,
MM.市区町村, MM.町域,MM.番地, MM.号, MM.建物名, MM.郵便番号,
MM.地区・常会, MM.下校コース, 
MM.父名, MM.父連絡先, MM.父連絡先電話番号, 
MM.母名, MM.母連絡先, MM.母連絡先電話番号, 
MM.緊急連絡先1, MM.緊急連絡先2, MM.緊急連絡先3, 
S1.学年 AS 在学兄弟姉妹クラス1, S1.名 AS 在学兄弟姉妹名1, 
S2.学年 AS 在学兄弟姉妹クラス2, S2.名 AS 在学兄弟姉妹名2, 
S3.学年 AS 在学兄弟姉妹クラス3, S3.名 AS 在学兄弟姉妹名3, 
MM.PTA役員, MM.備考, MM.除籍
FROM 
(
 (
   (
    T_児童マスタ AS MM
    LEFT JOIN T_児童マスタ AS S1
    ON (S1.電話番号 = MM.電話番号 AND S1.学年 <> MM.学年)
   )
  LEFT  JOIN T_児童マスタ AS S2
   ON (S2.電話番号 = MM.電話番号 AND S2.学年 <> MM.学年  AND S2.学年 <> S1.学年)
  )
LEFT JOIN T_児童マスタ AS S3
ON (S3.電話番号 = MM.電話番号 AND S3.学年 <> MM.学年 AND S3.学年 <> S1.学年 AND S3.学年 <> S2.学年)
)
;

タイトルRe^3: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83006
投稿日: 2016/10/18(Tue) 18:29
投稿者mayu
解決済: ON
こんにちは。

> できるだけクエリで処理しようとしています。

Accessを利用して、ご希望の形でデータを出力したい
というだけでしたら
hatenaさんがおっしゃるように
クエリ以外の方法を模索したほうがいいと私も思います。

どうしても SQLで表現なさりたいのでしたら
LIMIT, OFFSET, JOIN といった句だけに注目するのではなく、

ROW_NUMBER や RANK といった
ANSI-SQL標準の分析関数を Accessでどう実現するか
という点も考慮する必要がある、と申し上げておきます。

No:83003 を拝見する限りでは
{ ID }というカラムが 
{ T_児童マスタ }テーブルの固有識別子っぽいので
これを利用して回答しますね。


■ DDL
---------------------------------------------

CREATE TABLE T_児童マスタ
(
      ID       COUNTER       PRIMARY KEY
    , 学級     INT           NOT NULL
    , 氏名     VARCHAR( 50 ) NOT NULL
    , 電話番号 VARCHAR( 20 ) NOT NULL
) ;


■ データ例

 ID  学級  氏名   電話番号
--------------------------------------
 1   11   A   1111-11-1111
 2   21   B   2222-22-2222
 3   31   C   1111-11-1111
 4   41   D   2222-22-2222
 5   51   E   1111-11-1111
 6   61   F   3333-33-3333


# 以下に掲載する SQL は
#  ・ 構文が複雑になってもいい
#  ・ 実用的な速度が出るかどうかも不問
# という前提です。ご理解いただきますよう。


■ DML
-----------------------------------------------------------

SELECT ID1 As ID
     , 学級
     , 氏名
     , 電話番号
     , Max( iif( gnum = 1, cls ) ) As 在学兄弟姉妹クラス1
     , Max( iif( gnum = 1, nam ) ) As 在学兄弟姉妹名1
     , Max( iif( gnum = 2, cls ) ) As 在学兄弟姉妹クラス2
     , Max( iif( gnum = 2, nam ) ) As 在学兄弟姉妹名2
     , Max( iif( gnum = 3, cls ) ) As 在学兄弟姉妹クラス3
     , Max( iif( gnum = 3, nam ) ) As 在学兄弟姉妹名3
FROM
(
    SELECT x.ID1
         , x.学級
         , x.氏名
         , x.電話番号
         , x.cls
         , x.nam
         , x.ID2
         , count(1) as gnum
    FROM
    (
        SELECT t1.ID       As ID1
             , t1.学級
             , t1.氏名
             , t1.電話番号
             , t2.学級     As cls
             , t2.氏名     As nam
             , t2.ID       As ID2
        FROM T_児童マスタ t1
           , T_児童マスタ t2
        WHERE t1.電話番号 = t2.電話番号
          AND t1.ID <> t2.ID
    ) x
   ,
    (
        SELECT t1.ID       As ID1
             , t1.学級
             , t1.氏名
             , t1.電話番号
             , t2.ID       As ID2
        FROM T_児童マスタ t1
           , T_児童マスタ t2
        WHERE t1.電話番号 = t2.電話番号
          AND t1.ID <> t2.ID
    ) y
    WHERE x.ID1 = y.ID1
      AND x.ID2 >= y.ID2
    GROUP BY x.ID1
           , x.学級
           , x.氏名
           , x.電話番号
           , x.cls
           , x.nam
           , x.ID2
) q
GROUP BY ID1
     , 学級
     , 氏名
     , 電話番号

UNION ALL

SELECT ID
    , 学級
    , 氏名
    , 電話番号
    , Null
    , Null
    , Null
    , Null
    , Null
    , Null
FROM T_児童マスタ t1
WHERE EXISTS 
(
    SELECT 1 FROM T_児童マスタ t2 
     WHERE t2.電話番号 = t1.電話番号
       AND t2.ID <> t1.ID
    HAVING count(1) = 0 
)
ORDER BY 1 ;

-----------------------------------------------------------


■ 結果

 ID	学級  氏名  電話番号  クラス1  名1  クラス2  名2  クラス3  名3
-----------------------------------------------------------------------------------------
 1   11   A  1111-11-1111  31    C    51    E		
 2   21   B  2222-22-2222  41    D
 3   31   C  1111-11-1111  11    A    51    E		
 4   41   D  2222-22-2222  21    B
 5   51   E  1111-11-1111  11    A    31    C		
 6   61   F  3333-33-3333


では ☆


※ おまけ
> LEFT JOIN T_児童マスタ AS S1
>    ON (S1.電話番号 = MM.電話番号 AND S1.学年 <> MM.学年)

この結合条件だと
双子などで同学年の兄弟姉妹がいた場合、
家族を正確にリストアップできないのでダメだと思います。

タイトルRe^4: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83007
投稿日: 2016/10/18(Tue) 22:55
投稿者hatena
解決済: ON
mayuさん、こんばんは

すっ、すごいSQLですね。
複雑になるとは思ってましたが、想像以上でした。

ワークテーブルに出力する方法を提案したので、
作成してみました。

テーブル T_児童マスタ は mayuさんのもので。

ワークテーブルとして下記のテーブルを作成しておきます。
これも mayuさんのSQLの出力結果と同等のフィールド構成です。

■ DDL
---------------------------------------------

CREATE TABLE T_児童マスタ_兄弟
(
      ID       COUNTER       PRIMARY KEY
    , 学級     INT           NOT NULL
    , 氏名     VARCHAR( 50 ) NOT NULL
    , 電話番号 VARCHAR( 20 ) NOT NULL
    , クラス1  INT           NOT NULL
    , 名1      VARCHAR( 50 ) NOT NULL
    , クラス2  INT           NOT NULL
    , 名2      VARCHAR( 50 ) NOT NULL
    , クラス3  INT           NOT NULL
    , 名3      VARCHAR( 50 ) NOT NULL
) ;


■ 標準モジュール
---------------------------------------------

Public Sub 児童マスタ_兄弟_更新()
    Dim db As DAO.Database
    Dim rs_m As DAO.Recordset
    Dim rs_w As DAO.Recordset
    Dim i As Long
    
    Set db = CurrentDb
    db.Execute "DELETE T_児童マスタ_兄弟.* FROM T_児童マスタ_兄弟;", dbFailOnError
    db.Execute "INSERT INTO T_児童マスタ_兄弟 SELECT T_児童マスタ.* FROM T_児童マスタ;", dbFailOnError
    
    Set rs_w = db.OpenRecordset("SELECT * FROM T_児童マスタ_兄弟 ORDER BY ID")

    Do Until rs_w.EOF
        Set rs_m = db.OpenRecordset( _
            "SELECT * FROM T_児童マスタ WHERE 電話番号='" & rs_w!電話番号 & _
                "' AND ID <>" & rs_w!ID & " ORDER BY ID;")
        rs_w.Edit
        For i = 1 To 3
            If rs_m.EOF Then Exit For
            rs_w("クラス" & i) = rs_m!学級
            rs_w("名" & i) = rs_m!氏名
            rs_m.MoveNext
        Next
        rs_w.Update
        rs_m.Close
        rs_w.MoveNext
    Loop

    Set rs_m = Nothing
    rs_w.Close: Set rs_w = Nothing
    Set db = Nothing

End Sub


このプロシージャを実行すると T_児童マスタ_兄弟 に希望の形でデータを出力します。

タイトルRe^5: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83009
投稿日: 2016/10/19(Wed) 07:01
投稿者kamijo
解決済: ON
理想的な形で出力結果を得ることができました。
ありがとうございました。

あとは、兄弟関係はあるが、
・「電話番号」が異なる
・「姓」が異なる
・「住所」が異なる
などの条件も入ってきますが、
こちらはこのスレッドの意図から外れておりますので、
自分で考えるか、改めて別のスレッドを立てさせていただきたいと思います。

ありがとうございました。

タイトルRe^4: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83008
投稿日: 2016/10/19(Wed) 06:04
投稿者kamijo
解決済: ON
ありがとうございました。
理想の結果を得ることができました。

お忙しい中、とても助かりました。
改めて、ありがとうございました。


> ※ おまけ
> > LEFT JOIN T_児童マスタ AS S1
> > ON (S1.電話番号 = MM.電話番号 AND S1.学年 <> MM.学年)
>
> この結合条件だと
> 双子などで同学年の兄弟姉妹がいた場合、
> 家族を正確にリストアップできないのでダメだと思います。

そうですね。同学年の双子は実際に頻出する条件です。
五つ子×年子(6学年分)という場合、どうやってフィールドを増やそうか、
などと考えていましたが、上記の条件の方が重要度が高いですね。

タイトルRe^2: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83004
投稿日: 2016/10/18(Tue) 15:30
投稿者hatena
解決済: ON
> 出力結果をさらに利用したいとも考えておりますので、
> できるだけクエリで処理しようとしています。

クエリでできないことはないと思いますが、
とんでもなく複雑なものになりますよ。

出力結果をさらに利用とはどのように利用したいのでしょうか。

それによってはもっといい方法があるかも知れません。

例えば、
ワークテーブルにVBAで出力する
前回の回答で提示したクエリをエクセルに出力してエクセル側で加工する
など、いろいろ考えられます。

> 今、以下のように書いてみましたが、複数のJOINの使用によるエラーで躓いております。

最初に提示のテーブルとはフィールド構成がことなりますね。

最初のものでこちらではサンプルを作成して、いろいろ試しています。

途中で変更されると二度手間になります。最初のシンプルなのもので、
トライしてみてください。
それでうまくいったら、実際のテーブルに適応させるということ方針で。

タイトルRe^3: 兄弟関係の抽出 と LIMIT、OFFSETに代わるもの
記事No83005
投稿日: 2016/10/18(Tue) 18:14
投稿者kamijo
解決済: ON
折角書いていただいたのに申し訳ありませんでした。
初めに頂いた案で進めさせていただきたいと思います。

ありがとうございました。

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る