SQL Serverで前レコードの値を参照する

2019-11-25SQLServer

前レコードの値を参照するSQLを紹介します。

コーディング例

BEGIN
  CREATE TABLE #勤怠(
     氏名    VARCHAR(10)
    ,年月日  INT
    ,出勤    VARCHAR(5)
    ,退勤    VARCHAR(5)
  );

  INSERT INTO
    #勤怠
  VALUES
     ('佐藤',20190901,'8:53','18:07')
    ,('田中',20190901,'8:52','19:04')
    ,('山本',20190901,'8:49','18:12')
    ,('佐藤',20190902,'9:53','19:07')
    ,('田中',20190902,'9:52','20:04')
    ,('山本',20190902,'9:49','19:12')
    ,('佐藤',20190903,'10:53','20:07')
    ,('田中',20190903,'10:52','21:04')
    ,('山本',20190903,'10:49','20:12')
  ;

  WITH
    TBL AS (
      SELECT
         ROW_NUMBER() OVER (PARTITION BY 氏名 ORDER BY 年月日)	AS [枝番]
        ,*
      FROM
        #勤怠
    )
  SELECT
     THIS.氏名
    ,ISNULL(PREV.年月日,0)  AS [前日]
    ,ISNULL(PREV.出勤,'')   AS [前日出勤]
    ,ISNULL(PREV.退勤,'')   AS [前日退勤]
    ,THIS.年月日            AS [当日]
    ,THIS.出勤              AS [当日出勤]
    ,THIS.退勤              AS [当日退勤]
  FROM
    TBL THIS
  LEFT JOIN
    TBL PREV
    ON  THIS.氏名 = PREV.氏名
    AND THIS.枝番 = PREV.枝番 + 1
  ORDER BY
    THIS.氏名
  ;

  DROP TABLE #勤怠;
END

実行結果

氏名 前日 前日出勤 前日退勤 当日 当日出勤 当日退勤
佐藤 0 20190901 8:53 18:07
佐藤 20190901 8:53 18:07 20190902 9:53 19:07
佐藤 20190902 9:53 19:07 20190903 10:53 20:07
山本 0 20190901 8:49 18:12
山本 20190901 8:49 18:12 20190902 9:49 19:12
山本 20190902 9:49 19:12 20190903 10:49 20:12
田中 0 20190901 8:52 19:04
田中 20190901 8:52 19:04 20190902 9:52 20:04
田中 20190902 9:52 20:04 20190903 10:52 10:52

コーディングの23~30行目では、WITH句で氏名毎に年月日の昇順に並び替えて連番を付けた共通テーブル「TBL」を定義しています。
この共通テーブル「TBL」を2つ外部結合しているのですが、44行目の「THIS.枝番 = PREV.枝番 + 1」で前レコードと結合しています。
もしかすると、なぜ前レコードと結合するのに「+ 1」なのだろうと思われるかもしれません。私も最初はそうでした。
これは具体的に値を代入してみると一目瞭然です。
「THIS.枝番」を1とすると、この式を満たすには「PREV.枝番」は0でないといけません。
したがって、一つ前の枝番と結合することとなり、すなわち前レコードと結合することになります。

以上、「SQL Serverで前レコードの値を参照する」の紹介でした。

 

 

株式会社システムトラスト

人材募集中です。

システムトラストでは、システムエンジニア、プログラマーなどを随時募集中です。気軽にご相談ください。

お問合せ