MERGE文

ITコーディネータのシュウです。

IMG_0409

本当に久しぶりのアップになります。
システムの納品の対応で、ここ1ヶ月、精神的にも肉体的にもかなり忙しくて大変な状態でした。受託開発は、途中で変更や内容が膨らんで、予定通りに進めるのが難しく、本当に納期に間に合うのか?と冷や汗をいつもかくことが多いと思うのは私だけでしょうか?
そこのスコープの管理を如何にお客様との間できちんと行うのかがとても重要であることはいつも思っているのですが。。。
必死になってときに残業も惜しまず、頑張るのですが、なぜそうなっているのか事情がわかりにくい分野でもあり、意外に周りの目は冷たかったりして...トホホ。
でも、問題を一つ一つ解決していき、納期が見えてきたときの達成感も、我々しか味わえないものかも知れませんね。といっても、最後の検収が上がるまでは何があるかわからないので、まだ緊張が解けないこの頃です。

心の余裕が出てきたところで、またブログ再開します。

<本日の題材>
MERGE文

ある抽出結果の情報を指定のテーブルに登録したいけれど、既にそのデータ(キー情報が同じデータ)が登録したいテーブルに存在していたら、値の違う部分を更新し、まだ存在していなければ登録する、というような要件に出くわすときが時折あります。

このとき、データが既に存在しているかどうかを確認して、IF文でUPDATE、INSERTを切り分けるということは、結構面倒ですが、これを一度のSQLで実行することができるのが、MERGE文です。

簡単な例として、同一構造のテーブルを元データとして、データを更新、なければ登録する場合は、下記のようになります。

元データを商品テーブルA、登録・更新したいテーブルを商品テーブルBとします。

CREATE TABLE 商品テーブルA(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルA PRIMARY KEY (商品CD));

CREATE TABLE 商品テーブルB(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルB PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合:

SELECT * FROM 商品テーブルA;
merge_a

SELECT * FROM 商品テーブルB;
merge_b

商品テーブルAのデータを元に、商品テーブルBに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルBにデータを登録する場合に以下のMERGE文を実行します。

MERGE INTO 商品テーブルB
USING 商品テーブルA
ON (商品テーブルA.商品CD = 商品テーブルB.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = 商品テーブルA.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD, 商品名)
VALUES (商品テーブルA.商品CD, 商品テーブルA.商品名)
;

上記を実行した後に、商品テーブルBを確認すると、

SELECT * FROM 商品テーブルB;
merge_b_2

商品CD:00001 については、商品名のみ更新し、はじめは存在していなかった商品CD:00002 のデータが追加されているのが確認できます。(Oracleで検証)

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

縦に並んだ複数行のデータをカンマ区切りで横並びに表示

ITコーディネータのシュウです。

IMG_0402

写真は、前回と同じく「古代蓮の里」で撮った古代蓮の花です。
今年も暑い夏が続くと思えば、大型台風の影響で大雨などもあり、とても変わりやすく難しい天候ですね。台風などでこれ以上被害が出ないことを祈りつつ、天気予報をいつもチェックして注意しないといけないですね。

<本日の題材>
縦に並んだ複数行のデータをカンマ区切りで横並びに表示する方法(SQL Server)

縦に並んだ複数行のデータを、カンマ区切りで横並びに表示する方法について、最近SQL Serverで確認したので、忘れないようにアップしておきます。
SQL Serverでは、SQLクエリにFOR XML句というものを使うことで、SQLクエリの結果をXML形式で取得することができるのですが、今回はそれを使ってみる方法です。

例えば、地方とその地方に属する都道府県を、それぞれ、地域マスタ、府県マスタということで、下記のように持たせてみます。

SELECT * FROM dbo.地域マスタ
ORDER BY 地域CD;
地域マスタ

SELECT * FROM dbo.府県マスタ
ORDER BY 府県CD;
府県マスタ
… 続きは省略

ここで、上記の地域ごとの都道府県の情報を、カンマ区切りで横並びに表示させたいときに、以下のようにすると可能です。

SELECT
A.地域CD
, A.地域名
,(SELECT都道府県名 + ','
FROM dbo.府県マスタ
WHERE 地域CD = A.地域CD
ORDER BY 府県CD
FOR XML PATH('')) AS 都道府県名
FROM dbo.地域マスタ A
ORDER BY A.地域CD
;
府県結果

FOR XML句をPATHモードで指定し、スペース区切りの文字列を生成しながら、カンマでつなげるというかたちにすると、上記の結果のようになります。
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

外部結合について(2)

ITコーディネータのシュウです。 IMG_0412

前回紹介したさきたま古墳のそばに、古代蓮の里という公園(行田市の天然記念物であり市の花である「古代蓮(行田蓮)」をシンボルとする公園)があり、そこに行った時の写真です。6月下旬から8月上旬にかけて42種類12万株の花蓮を見ることができるということですが、花弁の数が少ない原始的な形態を持つ行田蓮は、約1400~3000年前の蓮であると言われています。 午前中、正午くらいまでが見ごろのようですよ! 何か神秘的な雰囲気もあり、結構きれいですね。

<本日の題材> 外部結合について(SQL Server 編) 前回実施した外部結合の内容を、SQL Server2012でも試してみましたので、報告します。

EMP表(従業員表)とDEPT表(部署表)をDEPTNOを条件として外部結合する例です。

1.左外部結合: LEFT [OUTER] JOIN の例 ([OUTER]は省略可能)

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.EMP E LEFT OUTER JOIN dbo.DEPT D ON E.deptno = D.deptno ORDER BY E.empno;

外部結合1_sqlserver

2.右外部結合: RIGHT [OUTER] JOIN の例

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.DEPT D RIGHT OUTER JOIN dbo.EMP E ON D.deptno = E.deptno ORDER BY E.empno;

外部結合1_sqlserver

3.完全外部結合: FULL [OUTER] JOIN

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.EMP E FULL OUTER JOIN dbo.DEPT D ON E.deptno = D.deptno ORDER BY E.empno;

完全外部結合_sqlserver

SQL Server2012でも同様な内容を確認しました。ちなみに、SQL Server2000の頃は、外部結合は *= という演算子を使っていましたが、データベースの互換性レベル(モード)が90以上ではこの演算子はサポートされなくなっていますね。 Oracleも昔は (+) というのを使用していましたが。。。

今日は以上まで

 

にほんブログ村 IT技術ブログへ
にほんブログ村

外部結合について

ITコーディネータのシュウです。

埼玉古墳__

映画「のぼうの城」で有名な忍城のある埼玉県行田市にあります、さきたま古墳に行ってきました。前方後円墳8基と円墳1基の5~7世紀ごろの大型古墳が残る、全国有数の大型古墳群です。写真は、直径105m、高さ約19mの日本最大の円墳である丸墓山古墳の頂上から見た稲荷山古墳です。

ところで、ここのところ、大手通信教育の会社の個人情報漏えい事件、マレーシア航空機の撃墜事件、中国の食品会社の使用期限切れ食肉使用問題、イスラエルとパレスチナの紛争など、様々な事件や問題が世界中で起きていることを感じます。

個人情報漏洩の件では、私も以前、協力会社のSEとして、ある大手会社の個人情報を扱う立場にいたこともありましたが、そこでは定期的に協力会社の社員にも個人情報に関する教育やWEBでのテストなどを実施して、危機意識を高める努力をしていたこともありますし、何か起きたときに真っ先に疑われるのが協力会社のSEということも感じていたので、とても注意して作業をしていました。

2003年に成立し、2005年から施工された個人情報保護法が、ビッグデータの利活用に関連して来年には改正される方向ですが、今回の件も考慮したかたちでの対応が必要と思われますね。

うちの子供もまさに今回問題となった通信教育を受けていて、名簿を買ったといわれる企業から急にDMが届いたので、何で急に?と不思議に思ったのを覚えています。
しかし、成人式が近い子供の振袖の宣伝やら、高校卒業が近い子供への各種専門学校からのDM、その他塾や通信教育の宣伝、本当にたくさんのDMが日々送られてきますね。どこの会社も頑張っているのはわかるのですが。
ときに、DMを包んでいるプラスチックゴミの分別など、手間がかかるなと感じてしまう私でした。

<本日の題材>
外部結合について

SELECT文で、複数のテーブルを結合して結果を出す場合に、ある条件で、両方のテーブルに一致する値を持つレコードだけでなく、一方のテーブルにしかないレコードも一緒に表示させたいケースも結構あります。今回は、外部結合についてちょこっと再確認してみます。

外部結合の種類と構文:
1.左外部結合: LEFT [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA LEFT [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

左側の表(テーブルA)の内容を全て抽出し、右側の表(テーブルB)は  条件で一致するレコードのみを表示します。

2.右外部結合: RIGHT [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA RIGHT [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

右側の表(テーブルB)の内容を全て抽出し、左側の表(テーブルA)は  条件で一致するレコードのみを表示します。

3.完全外部結合: FULL [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA FULL [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

左側の表(テーブルA)、右側の表(テーブルB)とも条件に一致しない  レコードも含めてすべて表示します。

例)よくある例としては、EMP表(従業員表)とDEPT表(部署表)があって、DEPTNOという部署コードが2つのテーブルをジョインするキーとなっている場合で、従業員と所属部署の一覧を見たいというときに外部結合を使用する例です。このとき、新しい従業員を雇ったのでEMP表にデータを登録するのですが、所属部署は設定せず、後から決めるというときに、その時点での一覧を見ると通常の結合(内部結合)では部署コードのない新入社員が抽出されないため、以下のように外部結合で抽出します。

新入社員の登録:
INSERT INTO EMP(empno,ename,hiredate) VALUES(8000,'SATOU','2014-07-20');

従業員と所属部署の一覧:
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM emp E
LEFT OUTER JOIN dept D ON E.deptno = D.deptno
ORDER BY E.empno;

外部結合1

上記は左外部結合ですが、これを右外部結合で表すと、
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM dept D
RIGHT OUTER JOIN emp E ON D.deptno = E.deptno
ORDER BY E.empno;

外部結合2

さらに、完全外部結合で表すと、DEPT表には存在するが、EMP表ではその部署コードを持つ従業員が存在しない部署も表示されます。
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM emp E
FULL OUTER JOIN dept D ON E.deptno = D.deptno
ORDER BY E.empno;

完全外部結合

今回はOracleを中心に確認しました。
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECTのCASE句について

ITコーディネータのシュウです。

1402143552101

たまにはITコーディネータについての情報もあったほうがいいかと思い、ITコーディネータ協会のHPから気づいた点を報告します。結構HPもリニューアルしていますね。

それで、1年以上前からイノベーションが創出できる次世代高度IT人材の育成について、独立行政法人情報処理推進機構(IPA)と共同で呼びかけ「IT融合人材育成連絡会」を立ち上げていました。そして「IT融合人材」の具体的な育成と組織のあり方についての検討成果についての最終報告書がホームページで公開されているようです。
http://www.itc.or.jp/news/inv20140325.html

イノベーションを創出できる人材を、あるプロセスに従うことで効果的に育成できれば?日本の将来にとっても本当に大きいことですよね。頑張れニッポン!

<本日の題材>
SELECTのCASE句について

今回は、最近システムを作り込むときに確認した内容で、SELECT文で複数の条件でのそれぞれの件数の結果を1つのSQLで実施する際に、CASE句を利用したケースについて記載します。(ORACLEです)

例)あるテーブル(受注実績テーブルとします)のデータで、あるフラグ(判定フラグとします)に「1」が立っているものと「0」のもの、及び全体の合計件数を抽出する。

SELECT
受注年月
, COUNT(CASE 判定フラグ WHEN '1' THEN 1 ELSE NULL END) AS 判定有
, COUNT(CASE 判定フラグ WHEN '0' THEN 1 ELSE NULL END) AS 判定無
, COUNT(*) 合計件数
FROM 受注実績
GROUP BY 受注年月
ORDER BY 受注年月;

oracle_case_1

他の方法としては、インラインビューを使用して下記のように行うこともできます。

SELECT
受注実績1.年月
, 受注実績1.件数 判定有
, 受注実績2.件数 判定無
, 受注実績3.件数 合計件数
FROM
(SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
WHERE 判定フラグ = '1'
GROUP BY 受注年月) 受注実績1
, (SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
WHERE 判定フラグ = '0'
GROUP BY 受注年月) 受注実績2
, (SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
GROUP BY 受注年月) 受注実績3
WHERE 受注実績1.受注年月 = 受注実績2.受注年月
AND 受注実績1.受注年月 = 受注実績3.受注年月
ORDER BY 受注実績1.受注年月;

oracle_case_2

 

でも、CASE句を使って抽出したほうが簡単ですね。
CASE文はいろいろなところで使用できますが、1つの例としてあげました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECTのTOP N分析(Oracle)

ITコーディネータのシュウです。

公園_4

サッカーワールドカップは、日本は十分力を出し切れず、一次予選敗退。本当に残念でした。最後のコロンビア戦は結構頑張っていたし、前半最後に追いついて、これはやってくれるんじゃないかと思ったのですが、カウンターを見事に決められ、終わってみれば完敗でした。アジアのチームはどこも1勝もできず、世界の壁はやはり厚いです。

また、忙しかったお客様の開発案件も、いよいよ運用開始が近くなってきました。まだ気が抜けません。

<本日の題材>
SELECTのTOP N分析について

前回、Microsoft SQL ServerのSELECTでのTOP句について見ましたが、今回はこれと同様のことをOracleで行う場合について考えてみます。Oracleの場合には、ROW_NUMBER関数を使うと同様なことが可能です。

例)今日の受注テーブルから受注番号順に先頭5件を抽出する。 SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY 受注NO) JNO, 受注NO, 受注日, 顧客番号, 決済金額 FROM 受注テーブル)
WHERE JNO <=5 ORDER BY JNO;

Oracle_row_number1

また、ROW_NUMBER関数を使用せずに、インラインビューを使用した問い合わせでも可能です。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 受注NO
)
WHERE ROWNUM <= 5
ORDER BY 受注NO;

Oracle_インラインビュー1

顧客番号順に並べた最初の5件ということにしたい場合には、以下のようになります。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 顧客番号
)
WHERE ROWNUM <= 5
ORDER BY 顧客番号;

Oracle_インラインビュー2

OracleにはTOP句はないですが、上記のような方法で同様のことができますね。
※ちなみに、SQL ServerでもROW_NUMBER関数は使えます。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECT TOP句について

ITコーディネータのシュウです。

1402143571315

いよいよサッカーワールドカップが始まりました。
今回は、逆転勝ちの試合も結構多いようですね。スペインがオランダに5対1で敗れた衝撃的な試合もありました。日本も初戦のコートジボアール戦は逆転負けしてしまいましたが、2戦目のギリシャ戦は、是非勝利を飾ってほしいものです。


<本日の題材>
SELECTのTOP句について

Microsoft SQL Serverの場合には、SELECTでのクエリの結果の行数を指定した行数、または割合(何パーセントか)に制限するTOP句という便利なものがあります。

今回はこれについて見てみたいと思います。

例)今日の受注テーブルから先頭5件を抽出する。
SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

sqlserver_9回目_1

例)先頭の5%を抽出するという場合は以下:
SELECT TOP 5 percent * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

これを、さらに、顧客番号順に並べた最初の5件を表示したいという場合には、以下のようになります。

SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112)
ORDER BY 顧客番号;

sqlserver_9回目_2

結構便利に使えます。
次回は、これをOracleで表現する場合を題材にする予定です。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

よもやまばなし

ITコーディネータのシュウです。

IMG_0839

娘が高校の遠足でディズニーランドに行ってきました。
同じ日に複数の学校から遠足で学生が来て、人も多かったみたいですが、楽しかったようです。

5月は、開発案件の関係で忙しく、緊張した日々が続きました。まだ一部残っている部分がありますが、最後まで気を抜かず、お客様に喜んでもらえるよう頑張らねば!

開発で使用しているツールはdbSheetといい、ExcelやAccessを使いながらWebシステムを構築できるツールです。なかなかおもしろいですよ。
http://www.newcom07.jp/dbsheetclient/index.html

it-coordinator①-1

今日は、少し話題を変えて我が家の様子などを紹介してみたいと思います。

仕事が遅くなって家に帰ると、妻がご飯を用意して待ってくれています。疲れて寝てるときもあったり、ときに遅くまで出かけてる時もありますが。。。

妻は、話をし始めたら止まらなくなるとてもおしゃべりタイプで、いろいろとその日の出来事などをおもしろおかしく教えてくれます。仕事のことでいっぱいだった頭も、いろいろな妻の話で気分転換されることが多く、内心はとても助かっています。

仕事で心配なことでもときに話してみると、結構冷静に、また前向きに元気づけてくれます。私よりも多分に気が強く、男勝りな部分もあるので、結構力強く背中を押してくれるので、ありがたい。ただ、機嫌が悪くなるとなかなか手が付けられなくなる部分もあるのが困りものですが。(ハイ~~)

また、子供たちも上は専門学校生、下は小学3年生と、なかなか幅が広く人数も多いのですが、それぞれに性格が違って、なかなかおもしろいです。
何も言わなくても一生懸命に勉強を頑張って、成績表をうれしそうに見せてくれる子もいれば、勉強はきらいだと、なかなかやる気が出ないけれど、遊ぶことについてはいろいろと知恵を使って新しい遊びを考えたりする子とか。。。

皆をまっすぐに育てるのが親の責任なんでしょうが、なかなか大変ですね。

ITコーディネータの資格の更新も6月末まで(毎年5月末までだったのが、今年は事情があって6月末まで)なので、遅れないように更新手続きもしないと!

ながながと痴話話につきあって頂き、ありがとうございました。
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL 比較関数(Greatest、Least)について

ITコーディネータのシュウです。 公園_4

お久しぶりです。

<本日の題材>
比較関数について

データベースの違いによって、あるDBでは使用できる関数が、別のDBでは使用できないケースがあります。その一例として、複数項目の最大値・最小値を取得するSQLを、今回は見てみたいと思います。

データベースがOracleの場合には、GREATEST関数・LEAST関数というのがあります。

例)金額に関する3つの項目の中から最大値を求める。具体的には

item_no | priceA | priceB | priceC |
-----------------------------------------------------
item01  |  6000 |  2000 |  3000
item02  |  1000 |  7500 |  4000
item03  |  1500 |  3000 |  8000

というテーブルがあった場合に、以下のような結果を出したいとします。

item_no | 最大値 | 最小値
------------------------------------------
item01  |  6000 |  2000
item02  |  7500 |  1000
item03  |  8000 |  1500

SQL> SELECT item_no
, GREATEST( priceA, priceB, priceC ) AS 最大値
, LEAST( priceA, priceB, priceC ) AS 最小値
FROM テーブル名;

greatest_ora

これは、MySQLでも同様の関数があります。
 GREATEST()、LEAST()

上記をSQL Serverで行おうとすると、同様の関数がないため、下記のようなSQLを実行するかたちになります。

SELECT  item_no, MAX(price1) AS 最大値, MIN(price2) AS 最小値 FROM
(           SELECT item_no, priceA AS price1, priceA AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceB AS price1, priceB AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceC AS price1, priceC AS price2 FROM テーブル名
) AS TBL
GROUP BY item_no
ORDER BY item_no;

sqlserver_greatest

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL日付関数(年齢計算)

ITコーディネータのシュウです。
untitled

春先に京都に行った時の金閣寺の写真です。せっかく撮っていたのでアップします。

金閣寺は正式には鹿苑寺といい、写真の金閣(舎利殿)を含む鹿苑寺が世界文化遺産として登録されています。現在の金閣は、三島由紀夫の小説金閣寺にもあるように、1950年の失火で国宝であった当時の金閣が全焼した後、1955年に再建されたものです。
再建にあたっては焼失直前の姿ではなく創建時の姿を再現するとの方針が採られ、現在のような金色に光る豪華なかたちになったとのこと。
ただ、国宝や重要文化財がこのような人為的な火災や、災害などで損失してしまうことは本当に残念です。文部科学省でもこのような文化財の総合防災対策などリスク管理についてはいろいろと検討・対策を取られているようです。

実は、最近仕事も忙しくなり、少々疲れ気味。久しぶりの投稿です。
体調管理も含め、リスク管理は本当に重要ですね!

it-coordinator①-1

<本日の題材>
日付関数についての続き(年齢計算)

システムでは、生年月日をデータベースの項目に持つテーブルが存在するケースが非常に多いですね。日付の計算に関連して、今回は年齢の計算について見てみたいと思います。

データベースがOracleであれ、SQL Server であれ、年齢計算においては、以下の2つの内容を確認することになるかと思います。
1)生年月日と基準日(通常は今日)の「年」の差
2)生年月日と基準日(通常は今日)の「月日」の大小を比較し、基準日が誕生日に達してなければ -1

SQLとしてはいろいろなやり方があると思いますが、以下に例を挙げます。

Oracle:
SELECT
EXTRACT(YEAR FROM 基準日) - EXTRACT(YEAR FROM 誕生日) -CASE WHEN TO_CHAR(基準日, 'MMDD') < TO_CHAR(誕生日, 'MMDD') THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

SQL Server:
SELECT YEAR(基準日) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , 基準日, 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

※YEAR(基準日) - YEAR(生年月日) は、DATEDIFF(YEAR,生年月日,GETDATE())でも同じ

例)
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, YEAR(GETDATE()) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , getdate(), 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

年齢結果2_result

また、簡単に計算する方法として、日付型(yyyymmdd)を8桁数値に変換後に10000で割って小数点を切り捨てる方法もよく使われるようです。

Oracle:
SELECT TRUNC((TO_CHAR(基準日,'YYYYMMDD') - TO_CHAR(生年月日, 'YYYYMMDD')) /10000, 0) AS 年齢 FROM テーブル名;

※TRUNC関数は切り捨てで、第2引数が少数第何位での切り捨てかを示す。

SQL Server:
SELECT
ROUND((CONVERT(INT,CONVERT(VARCHAR(8),基準日,112))-CONVERT(INT,CONVERT(VARCHAR(8),生年月日,112))/10000,0,1) AS 年齢
FROM テーブル名;

※ROUND関数の第3引数が0以外の場合は切り捨てになります。また、FLOOR関数を使用すると、引数に対して、それ以下で最も大きい整数値を取るので同じことになります。
※閏年生まれの方も、3月1日時点できちんと年齢が上がります。

例)
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, ROUND((CONVERT(INT,CONVERT(VARCHAR,getdate(),112))-CONVERT(INT,CONVERT(VARCHAR,生年月日,112)))/10000,0,1) AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

結果は最初の処理と同じ。

注意)日本の法律(年齢計算に関する法律)上は、年齢というのは誕生日の前日で加算されるもののため、計算結果の利用目的によっては上記のような計算に1日加算するなど調整が必要になります。年齢を元に何らかの計算するような場合、年齢計算を法律に則って行うのか、一般慣習に基づいて行っていいのかよく調査してから計算式を決定する必要があります。

★また、EXCELからデータをアクセスできる環境であれば、EXCELのほうで年齢計算をできる(DATEDIF関数)ので、EXCEL側で年齢を表示させることが簡単にできます。
年齢結果Excel_result
【書式】
DATEDIF(開始日,終了日,単位)
【単位の引数】
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の日数
"MD" 開始日から終了日までの日数
"YM" 開始日から終了日までの月数
"YD" 開始日から終了日までの日数

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

主にSQLについて書いていきたいと思います。