none
NULLを直前のレコードで埋めたい RRS feed

  • 質問

  • SQL Server 2008 Standard

    会計系のデータベースで以下のようなテーブルがあります。


    仕訳明細表

    ID 伝票No 行No 貸借 科目 金額
    1 1 1 0 10現金 100
    2 1 2 1 11預金 100
    3 2 1 0 10現金 300
    4 2 2 0 13売掛 500
    5 2 1 1 80売上 50
    6 2 2 1 80売上 150
    7 2 3 1 80売上 250
    8 2 4 1 80売上 350

    上記を貸借に展開しました。

    SELECT
     	ISNULL(L.伝票No,R伝票No)	AS 伝票No,
     	ISNULL(L.行No,R行No)  	AS 行No,
     	L.科目	AS 借方科目,
     	L.金額	AS 借方金額,
     	R.科目	AS 貸方科目,
     	R.金額	AS 貸方金額
    FROM
    	仕訳明細表 AS L	FULL OUTER JOIN
    	仕訳明細表 AS R
    	ON L.伝票No=R.伝票No AND L.行No,R行No AND L.貸借=(R.貸借-1)*-1
    ORDER BY
    	伝票No,行No
    

    伝票No 行No 借方科目 借方金額 貸方科目 貸方金額
    1 1 10現金 100 11預金 100
    2 1 10現金 300 80売上 50
    2 2 13売掛 500 80売上 150
    2 3 (NULL) (NULL) 80売上 250
    2 4 (NULL) (NULL) 80売上 350

    上記の借方金額はISNULL( ,0)で0円にすれば良いのですが,借方科目の(NULL)を,直前の結果レコード「13売掛」で埋める必要があります。何か美しい方法はありませんでしょうか。


    前提条件としては;

    • 行No=1のレコードは,貸方・借方とも必ず存在する
    • MAX(行No)は不定で, 30行超の実データも存在する
    • (NULL)は貸方・借方とも発生し得る
    • たとえば1行目と3行目は存在し,2行目と4行目が(NULL)というパターンもあり
    • 貸借の相関(金額の大小や存否)は全く考慮にいれる必要は無い
    です。よろしくお願いします。

    2010年6月8日 4:45

回答

  • 昔からある方法は、
    「自レコードよりキーが小さいレコードの中で最大のキーのレコード」の「13売掛」を
    自レコードの1項目として取得しておき、
    条件に応じてその項目を出力するというものです。

    かなり複雑な SQL になるので美しいとは言い難いです。
    (もしかして上記の情報はご存知のうえでの質問だったでしょうか。)

    中間テーブルを作ってよいのであれば、行番号を持つなどして
    単純化できますが、SQL はふたつに分かれると思われます。

    再帰を使うような問題じゃないと思いますが
    もしかして美しくはなるのかも?

    • 編集済み anningo 2010年6月8日 5:57 訂正「単純可」→「単純化」
    • 回答としてマーク Sedvicious 2010年6月8日 8:32
    2010年6月8日 5:17
  • ご提示されたSQLに誤りがありますので、想像しながら以下のようにしてみました。

    SELECT
       ISNULL(L.伝票No,R.伝票No)  AS 伝票No,
       ISNULL(L.行No,R.行No)   AS 行No,
       case when L.科目 is null then (select top 1 T.科目 from 仕訳明細表 as T
                       where T.伝票No=R.伝票No and T.行No < R.行No and T.貸借=0
                       order by T.行No desc)
                   else L.科目
       end AS 借方科目,
       L.金額  AS 借方金額,
       R.科目  AS 貸方科目,
       R.金額  AS 貸方金額
    FROM
      仕訳明細表 AS L  full OUTER JOIN
      仕訳明細表 AS R
      ON L.伝票No=R.伝票No AND L.行No=R.行No AND L.貸借=(R.貸借-1)*-1
    where isnull(L.貸借,0)=0
    ORDER BY
      伝票No,行No

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク Sedvicious 2010年6月8日 8:32
    2010年6月8日 7:59

すべての返信

  • 昔からある方法は、
    「自レコードよりキーが小さいレコードの中で最大のキーのレコード」の「13売掛」を
    自レコードの1項目として取得しておき、
    条件に応じてその項目を出力するというものです。

    かなり複雑な SQL になるので美しいとは言い難いです。
    (もしかして上記の情報はご存知のうえでの質問だったでしょうか。)

    中間テーブルを作ってよいのであれば、行番号を持つなどして
    単純化できますが、SQL はふたつに分かれると思われます。

    再帰を使うような問題じゃないと思いますが
    もしかして美しくはなるのかも?

    • 編集済み anningo 2010年6月8日 5:57 訂正「単純可」→「単純化」
    • 回答としてマーク Sedvicious 2010年6月8日 8:32
    2010年6月8日 5:17
  • annigngoさん、ご助言いただきありがとうございます。

    前者の力技は、ご推察のとおり実行コスト面でも難があり、避けたいところなのです。

    後者の中間テーブルも、できればビューで実装したいという希望があります。

    わがままばかりで申し訳ありません。

     

    貸借に展開する段階で自己結合しているので、ここの書きようによっては良い方法があるかと思ったのですが、私の足りない頭では良い案が思いつかず、お知恵を拝借したいと思った次第です。

    2010年6月8日 5:49
  • ご提示されたSQLに誤りがありますので、想像しながら以下のようにしてみました。

    SELECT
       ISNULL(L.伝票No,R.伝票No)  AS 伝票No,
       ISNULL(L.行No,R.行No)   AS 行No,
       case when L.科目 is null then (select top 1 T.科目 from 仕訳明細表 as T
                       where T.伝票No=R.伝票No and T.行No < R.行No and T.貸借=0
                       order by T.行No desc)
                   else L.科目
       end AS 借方科目,
       L.金額  AS 借方金額,
       R.科目  AS 貸方科目,
       R.金額  AS 貸方金額
    FROM
      仕訳明細表 AS L  full OUTER JOIN
      仕訳明細表 AS R
      ON L.伝票No=R.伝票No AND L.行No=R.行No AND L.貸借=(R.貸借-1)*-1
    where isnull(L.貸借,0)=0
    ORDER BY
      伝票No,行No

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク Sedvicious 2010年6月8日 8:32
    2010年6月8日 7:59
  • trapemiyaさんありがとうございました。またプロトタイプの誤記も失礼いたしました。

    annnigoさんからご指示いただいた「自レコードよりキーが小さいレコードの中で最大のキーのレコード」、FROMで結合することばかり考えていたのですが、ご指南頂きましたCASE WHEN ~ TOP1 ~DESCの利用というのは思いついておらず、試行してみたところパフォーマンス的にも問題なかったので、これでいかせて頂こうかと思います。

    お二人様、大変勉強させて頂いてありがとうございます。

     

    2010年6月8日 8:32
  • SQLServer2008の環境がないので、PostgreSQL9.0で
    SQLServerの分析関数の使用例(http://oraclesqlpuzzle.hp.infoseek.co.jp/sqlserver2008-sql1-olap.html)
    の「16. Lag関数,Lead関数 (1行前と1行後)」を見ながら作ってみました。

    Limit句をTop句に置き換えれば、多分SQLServer2008で動くと思います。

    with work(ID,伝票No,行No,貸借,科目,金額) as(
    values(1,1,1,0,'10現金',100),
       (2,1,1,1,'11預金',100),
       (3,2,1,0,'10現金',300),
       (4,2,2,0,'13売掛',500),
       (5,2,1,1,'80売上', 50),
       (6,2,2,1,'80売上',150),
       (7,2,3,1,'80売上',250),
       (8,2,4,1,'80売上',350)),
    tmp(伝票No,行No,借方科目,借方金額,貸方科目,貸方金額,rn) as(
    select 伝票No,行No,
    max(case 貸借 when 0 then 科目 end),
    max(case 貸借 when 0 then 金額 else 0 end),
    max(case 貸借 when 1 then 科目 end),
    max(case 貸借 when 1 then 金額 else 0 end),
    Row_Number() over(order by 伝票No,行No)
     from work
    group by 伝票No,行No)
    select 伝票No,行No,
    (select b.借方科目
      from tmp b
     where b.rn <= a.rn
      and b.借方科目 is not null
     order by b.rn desc Limit 1) as 借方科目,
    借方金額,貸方科目,貸方金額
     from tmp a
    order by rn;
    
     伝票no | 行no | 借方科目 | 借方金額 | 貸方科目 | 貸方金額
    --------+------+----------+----------+----------+----------
       1 |  1 | 10現金  |   100 | 11預金  |   100
       2 |  1 | 10現金  |   300 | 80売上  |    50
       2 |  2 | 13売掛  |   500 | 80売上  |   150
       2 |  3 | 13売掛  |    0 | 80売上  |   250
       2 |  4 | 13売掛  |    0 | 80売上  |   350
    
    2010年6月8日 10:50
  • なるほど。Row_Number() を使う方法もあるのですね。勉強になりました。以下にSQL Server版に修正させていただいたSQLを載せておきます。 

    with tmp (伝票No,行No,借方科目,借方金額,貸方科目,貸方金額,rn) as (
    select 伝票No,行No,
    max(case 貸借 when 0 then 科目 end),
    max(case 貸借 when 0 then 金額 else 0 end),
    max(case 貸借 when 1 then 科目 end),
    max(case 貸借 when 1 then 金額 else 0 end),
    Row_Number() over(order by 伝票No,行No)
     from 仕訳明細表
    group by 伝票No,行No)
    
    select a.伝票No,a.行No,
    (select top 1 b.借方科目
     from tmp b
     where b.rn <= a.rn
     and b.借方科目 is not null
     order by b.rn desc) as 借方科目,
    借方金額,貸方科目,貸方金額
     from tmp a
    order by rn;

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 編集済み trapemiya 2010年6月9日 3:11 CTEのテーブル名をtmpに合わせた
    2010年6月9日 2:36
  • SQLServer版を作っていただき、ありがとうございます。
    ソートキーが複数なので、相関サブクエリを使う下準備として、
    Row_Number()関数を使って、ソートキーを1つにしているのです。


    余談ですが、top句は式を括弧でくくるのが推奨されてますので
    括弧でくくったほうがいいかもしれません。
    いいかえれば
    select top 1 b.借方科目 from 以下略 ではなく
    select top (1) b.借方科目 from 以下略 と記述したほうがいいかもしれません。

    http://msdn.microsoft.com/ja-jp/library/ms189463(loband).aspx
    >旧バージョンとの互換性のため、
    >SELECT ステートメントで TOP を使用する場合は expression をかっこで囲まなくても処理が行われますが、
    >これは推奨されません。

     

    2010年6月9日 10:56
  • SQLServer版を作っていただき、ありがとうございます。

    作ったというよりSQL Server 2008で動作確認をしただけですね(^^; ほとんどAketi Jyuuzouさんが書かれている通りのSQLです。

    余談ですが、top句は式を括弧でくくるのが推奨されてますので
    括弧でくくったほうがいいかもしれません。

    見逃していました。SQL Server 2005からそのように強化されていたのですね。しかもtop句が変数として使えるようになっていたとは! PIVOTやROW_NUMBER、CTEの追加に比べれば地味ですが、待ち望んでいた素晴らしい強化です。
    またまた勉強になりました。ありがとうございました。


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