SQL Serverで複合連番を自動採番する:年+シリアル/SqlServer

  • このエントリーをはてなブックマークに追加
  • LINEで送る

仕事でSQLServerを扱っていたのですが、久しく触っていなかったので大分忘れてしまいました。

単純な自動採番であれば該当列をint系に割り振り、プロパティの「IDENTITYの指定」で(IDである)を「はい」にすればいいのですが、年+シリアル(連番)のような組み合わせだとそう簡単にいきません。

また、SQLで自動採番というとmax関数の利用を思いつきますが、年+シリアル(連番)となると、こちらも簡単ではありません。

と言いつつも、変数を利用すると比較的簡単に構成できました。

年(2桁)+シリアル(4桁)を取り扱うため、2014年は「140001」~「149999」の範囲です。シリアル途中で年が切り替わると(2015年になると)「150001」から再スタートさせます。

簡単な流れですが、

  1. 変数@MAXは6桁(年2桁+シリアル4桁)を確保します。
  2. select文で現在の最大値を取得します。
  3. 未採番、もしくは今年分が未採番であれば西暦年4桁の下2桁+シリアル初期値(0001)とします
  4. 採番済みであれば、現在地に1を加算します。
  5. レコードを登録します。

となります。

同じ連番を複数のレコードに同時登録したい場合は

とvaluesを増やすことにより対応できます。

<

h3 class=”memo”>
使用したSQL構文/関数のメモ

declare 変数 型:変数を指定の型で宣言します。
set 変数=値:変数に値を代入します。
case when 条件 then 真処理 else 偽処理 end:SqlServer上での条件分岐です。
year():サーバー日付の年(int)を返します。
getdate():サーバーの日付(datetime)を返します
datename():datetime型から指定した表現の文字列を返します。「yy」は年を意味します。
right():文字列の右側から指定した長さの文字列を返します。

  • このエントリーをはてなブックマークに追加
  • LINEで送る

SNSでもご購読できます。

スポンサー リンク

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください