SQL文（PL・SQL）

質問

給料表として属性が社員ID、給料適用開始年度、給料適用開始月、給料があります。例えば、
1行目→101、2008、8、200,000
2行目→101、2008、10、210,000
3行目→102、2007、12、190,000
というデータがあった場合に2008年の9月の社員全体の給料合計を求めたいんです。この場合社員101の人は9月時点の給料は200,000で102の人は190,000のままです。このような場合どうSQL or PL/SQLを書けばこの情報を得ることできますか?

回答No.4

k_o_r_o_c_h_a_n · ベストアンサー

私が設計するなら・・

社員ID,給料,適用開始年月,適用終了年月
101,\100,200801,200807
101,\110,200808,999999
102,\200,200801,999999
103,\200,200801,200805
な感じかな。

更新するときは、適用終了年月が999999のデータを、実際の終了年月で更新し、
新たなレコードを作成する。（新たなレコードの適用終了年月は999999）
新入社員は、新たなレコードを作成するだけ。
社員が辞める場合は、終了年月を埋めるだけ。

データを検索する場合は、
where 調査したい年月>=適用開始年月 and (調査したい年月<=適用終了年月 or 適用終了年月=999999)
で探す。

データは、会社の会計年度に合わせるのでなく、歴日で持ち、会計年度で取り扱う必要箇所が多いのであれば、
会計年度と会計期間の変換機能を用意し、人の目に触れるところだけ、会計年度に変換し表示します。
会社の会計年度は変更される場合があるけれど、データまで会計年度で管理すると、変更があった場合に煩わしいから。

これは、日付の和暦と西暦と同じような話で、ユーザインタフェースが和暦であっても、
データとしては和暦でなく西暦で管理するのと同じと考えます。

補足

なるほどですね。ありがとうございます。ここで型について無知なので教えてください。200809などはDATE型で格納したほうがいいですよね？？(charaでもいけるような気が・・・でもDATE型の方がいいですよね？）その時999999はデータ型として認識されるのでしょうか？

回答No.3

k_o_r_o_c_h_a_n

>2008年の9月の社員全体の給料合計を求めたいんです。

これに対するＳＱＬを書いたのですが、給料適用開始年でなく年度だったんですね。
表の持ち方は、年度制でない方が良いと思いますよ。
（先に示したSQLは、１月が年度の開始月の場合でしか正しく評価できません）

>条件としては、指定した月の全社員の給料がいつでもみれることです。

ということなら、

select 社員ID,給料適用開始年,給料適用開始月,給料
from
(
select
社員ID,給料適用開始年,給料適用開始月,給料,
row_number() over(partition by 社員ID order by 給料適用開始年 desc,給料適用開始月 desc) R
from 給料表
where to_date(to_char(給料適用開始年,'FM9999')||to_char(給料適用開始月,'FM99'),'yyyymm') <= to_date('2008/09','yyyy/mm')
)
where R=1
;

で、いつでも指定した年月時点の社員別給与が一覧できます。

>しかし理解できませんでした・・・。
特別難しいとこはしていない、普通のSQL文です。
理解できない部分はマニュアル読むだけで理解できると思いますよ。
（サブクエリ部分だけを実行すると理解しやすいと思いますけどね）

お礼

ありがとうございます。もしk_o_r_o_c_h_a_nさんが社員ID、給料を必須の属性として他に属性を増やしてよいという条件ならどのように表を設計しますか？？ただし給料の変更が決まったときにしかレコードを増やせなくて、年度制でこれを実現したいのです。何かアドバイスお願いいたします。

回答No.2

k_o_r_o_c_h_a_n

select sum(給料)
from
(
select
給料,
row_number() over(partition by 社員ID order by 給料適用開始年 desc,給料適用開始月 desc) R
from 給料表
where to_date(to_char(給料適用開始年,'FM9999')||to_char(給料適用開始月,'FM99'),'yyyymm') <= to_date('2008/09','yyyy/mm')
)
where R=1
;

※サブクエリ中の条件の to_date('2008/09','yyyy/mm') の部分に求めたい年月(例では、2008/09)を指定してください。

お礼

ありがとうございます。しかし理解できませんでした・・・。実のことをいうとテーブルの属性をかえることができます。条件としては、指定した月の全社員の給料がいつでもみれることです。こちらの要望としては、給料を変更したときだけデータを更新する、いつ給料がかわるかはわからない、給料が下がることもある、の３点です。あとはこれを一つのテーブルで実現したいのです。何か案があればよろしくお願いいたします。PL/SQLでも構いません。

回答No.1

PXU10652

実現が難しい理由は、「給料適用開始年度」と「給料適用開始月」が別れていることと、月別のデータがないと言う点です。

　前提条件として、給料は年月が進に連れて上昇するものだとします。「給料適用開始年度」と「給料適用開始月」を連結してYYYYMMの型式にして、200809以前で社員ID毎に給与の最大値を「200809給与」というテーブルに出力します。これで、９月時点の人別の給料が分かります。

「SELECT 給料表.社員ID, Max(給料表.給料) AS 給料の最大 INTO 200809給与 FROM 給料表 WHERE ((([給料表]![給料適用開始年度] & Right("0" & [給料表]![給料適用開始月],2))<=200809))
GROUP BY 給料表.社員ID;」

　「200809給与」さえ出来てしまえば、サマリーしてしまえば終わりです。
「SELECT Sum([200809給与].給料の最大) AS 給料の合計 FROM 200809給与;」

　前提条件（給料は年月が進に連れて上昇する）が崩れる場合は、データに適用終了年月のデータがないとデータを抽出できません。「適用開始年月」と「適用終了年月」が「YYYYMM」の型式で入っていたら、この前提条件を考慮する必要が無く、かつ一発で答えが求められます。

「SELECT Sum(給料表.給料) AS 給料の合計 FROM 給料表 WHERE (((給料表.適用開始年月)<="200809") AND ((給料表.適用終了年月)>="200809"));」

お礼

ご丁寧にありがとうございます。実のことをいうとテーブルの属性をかえることができます。条件としては、指定した月の全社員の給料がいつでもみれることです。こちらの要望としては、給料を変更したときだけデータを更新する、いつ給料がかわるかはわからない、給料が下がることもある、の３点です。あとはこれを一つのテーブルで実現したいのです。何か案があればよろしくお願いいたします。