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

2019/11/252021/02/04データベースSQLServer

前レコードの値を参照する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

実行結果

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

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

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

お問合せ