none
SELECT文でのテーブルより加工したデータの抽出 RRS feed

  • 質問

  • ターチと申します、お世話になります。

    (SQLSERVER2005)
    テーブルのデータの抽出で戸惑ってしまい投稿しました。

    売上履歴を参照し請求金額一覧を求めるSELECT文を作成
    したいと思っております。

    売上先IDと請求先IDを比較して請求金額を売上先ID別に算出
    したいと考えております。
    売上先IDと請求先IDは必須項目とします。

    (売上履歴テーブルデータ)
     売上先ID 請求先ID  売上金額
    1  0001      0001      1000
    2  0002      0006      1000
    3  0002      0006      1000
    4  0003      0007      1000
    5  0003      0007      1000
    6  0004      0006      1000
    7  0005      0005      1000
    8  0006      0006      1000
    9  0007      0007      1000  

    (SELECT文にて表現したい結果)
     売上先ID 請求先ID  売上金額 請求金額
    1  0001      0001      1000      1000
    2  0002      0006      2000         0
    3  0003      0007      2000         0
    4  0004      0006      1000         0
    5  0005      0005      1000      1000
    6  0006      0006      1000      4000
    7  0007      0007      1000      3000

    集計結果レコードをSELECT文で抽出したいと考えております。
    宜しくお願いします。

    2009年7月13日 0:41

回答

  • たぶん答えは書けるけど…いちいち聞くんですか?

    考え方として(売上先ID 請求先ID  売上金額)の集計表と(請求先ID  請求金額)の集計表をそれぞれSELCT文で実現できますか?
    それを結合すればご質問の表が完成します。

    ちなみに条件が足りてなくて、売上先IDに対して請求先IDが一意に定まるかどうかによってSELECT文が変わってくると思います。
    • 回答としてマーク ターチ 2009年7月13日 3:43
    2009年7月13日 2:21
  • こんにちは、nagino です。

    売上先 ID と 請求先 ID は 多対 1 でしょうか。
    請求金額は、請求先 ID 別に売上金額を集計し、その請求先 ID と一致する売上先 ID の行に表示するということでしょうか。

    そうであれば、色々書き方はありますが、いくつかあげるとすると以下のような SELECT 文で抽出できます。

    SELECT
        売上.売上先ID,
        売上.請求先ID,
        SUM(売上.売上金額) 売上金額,
        ISNULL(SUM(請求.請求金額), 0) 請求金額
    FROM 売上履歴テーブル 売上
    LEFT JOIN (
        SELECT
            請求先ID,
            SUM(売上金額) 請求金額
        FROM 売上履歴テーブル
        GROUP BY 請求先ID
        ) 請求
        ON 売上.売上先ID = 請求.請求先ID
    GROUP BY 売上.売上先ID, 売上.請求先ID
    ORDER BY 売上.売上先ID

    SELECT DISTINCT
        売上.売上先ID,
        売上.請求先ID,
        SUM(売上.売上金額) OVER(PARTITION BY 売上.売上先ID) 売上金額,
        ISNULL(SUM(請求.売上金額) OVER(PARTITION BY 売上.売上先ID), 0) 請求金額
    FROM 売上履歴テーブル 売上
    LEFT JOIN 売上履歴テーブル 請求
        ON 売上.売上先ID = 請求.請求先ID
    ORDER BY 売上.売上先ID

    他にもいろいろ書き方はありますし、細かいところは仕様・環境に合わせて手を入れてください。


    MCITP(Database Developer/Database Administrator)
    • 回答としてマーク ターチ 2009年7月13日 3:43
    2009年7月13日 2:19

すべての返信

  • こんにちは、nagino です。

    売上先 ID と 請求先 ID は 多対 1 でしょうか。
    請求金額は、請求先 ID 別に売上金額を集計し、その請求先 ID と一致する売上先 ID の行に表示するということでしょうか。

    そうであれば、色々書き方はありますが、いくつかあげるとすると以下のような SELECT 文で抽出できます。

    SELECT
        売上.売上先ID,
        売上.請求先ID,
        SUM(売上.売上金額) 売上金額,
        ISNULL(SUM(請求.請求金額), 0) 請求金額
    FROM 売上履歴テーブル 売上
    LEFT JOIN (
        SELECT
            請求先ID,
            SUM(売上金額) 請求金額
        FROM 売上履歴テーブル
        GROUP BY 請求先ID
        ) 請求
        ON 売上.売上先ID = 請求.請求先ID
    GROUP BY 売上.売上先ID, 売上.請求先ID
    ORDER BY 売上.売上先ID

    SELECT DISTINCT
        売上.売上先ID,
        売上.請求先ID,
        SUM(売上.売上金額) OVER(PARTITION BY 売上.売上先ID) 売上金額,
        ISNULL(SUM(請求.売上金額) OVER(PARTITION BY 売上.売上先ID), 0) 請求金額
    FROM 売上履歴テーブル 売上
    LEFT JOIN 売上履歴テーブル 請求
        ON 売上.売上先ID = 請求.請求先ID
    ORDER BY 売上.売上先ID

    他にもいろいろ書き方はありますし、細かいところは仕様・環境に合わせて手を入れてください。


    MCITP(Database Developer/Database Administrator)
    • 回答としてマーク ターチ 2009年7月13日 3:43
    2009年7月13日 2:19
  • たぶん答えは書けるけど…いちいち聞くんですか?

    考え方として(売上先ID 請求先ID  売上金額)の集計表と(請求先ID  請求金額)の集計表をそれぞれSELCT文で実現できますか?
    それを結合すればご質問の表が完成します。

    ちなみに条件が足りてなくて、売上先IDに対して請求先IDが一意に定まるかどうかによってSELECT文が変わってくると思います。
    • 回答としてマーク ターチ 2009年7月13日 3:43
    2009年7月13日 2:21
  • すいませんでした、売上先IDに対して請求先IDが一意です。
    OVER(PARTITION BY ..)は知りませんでした
    今後の参考にさせていただきます。

    無事解決することができました、ありがとうございます。
    2009年7月13日 3:49
  • 解決されたとのことですが、naginoさんのSQLですと例えば売上先IDに0006がもう一件あると請求金額が二重に集計されてしまいそうなのですが、いかがでしょうか??? うまく行っているとのことですので、私が仕様をうまく飲み込めていないのかもしれません・・・


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年7月13日 4:20
  • こんにちは、nagino です。

    さすがといいますか、trapemiya 様のご指摘の通りです。
    ご指摘ありがとうございます、助かりました。
    確認不足で書くのはよろしくありませんね。

    佐祐理様のコメントもありますし、きっとご本人で適切に修正されていると思いますが、参考資料という点から真面目に書くと以下のような感じでしょうか。

    SELECT DISTINCT
        基本.売上先ID,
        基本.請求先ID,
        ISNULL(売上.売上金額, 0) 売上金額,
        ISNULL(請求.請求金額, 0) 請求金額
    FROM 売上履歴テーブル 基本
    LEFT JOIN (
        SELECT
            売上先ID,
            SUM(売上金額) 売上金額
        FROM 売上履歴テーブル
        GROUP BY 売上先ID
        ) 売上
        ON 基本.売上先ID = 売上.売上先ID
    LEFT JOIN (
        SELECT
            請求先ID,
            SUM(売上金額) 請求金額
        FROM 売上履歴テーブル
        GROUP BY 請求先ID
        ) 請求
        ON 基本.売上先ID = 請求.請求先ID
    ORDER BY 基本.売上先ID

    MCITP(Database Developer/Database Administrator)
    2009年7月13日 5:14
  • こちらこそnaginoさんの書き込みでいつも勉強させていただいています。大変ありがたく思っております。

    さて、パフォーマンス的にはどうかわかりませんが、以下のようなSQLも書けると思います。

    SELECT
        売上.売上先ID,
        売上.請求先ID,
        SUM(売上.売上金額) 売上金額,
        (select isnull(SUM(売上金額), 0) from 売上履歴テーブル 請求 where 売上.売上先ID = 請求.請求先ID) 請求金額
    FROM 売上履歴テーブル 売上
    GROUP BY 売上.売上先ID, 売上.請求先ID
    ORDER BY 売上.売上先ID


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年7月13日 6:09