テーブル結合時の集計関数について

質問

環境:oracle 9i

Oracleを触り始めて1週間程度の初心者です。
以下の3つのテーブルを外部結合し、集計結果を取得するSQL文をご教授いただけないでしょうか。COUNT関数やGROUP BY句などを使えばよさそう、というところまではわかるのですが、そこから先がどうも見当がつきません。。
どうぞ宜しくお願い致します。

【tbl1】
日付　　社員名　　摘要　　労働時間
date　sya_nm　status　　hours
------------------------------------------
8/10　　田中　　出勤　　　8
8/15　　田中　　欠勤　　　0
8/20　　田中　　出勤　　　8
9/10　　田中　　出勤　　　8
8/20　　佐藤　　遅刻　　　6
8/25　　高橋　　出勤　　　8

【tbl2】
社員名　事業所　区分　時給
sya_nm　jig_cd　div　pay
------------------------------------------
田中　　　1　　在籍　1000
佐藤　　　1　　在籍　1000
鈴木　　　1　　退職　1000
高橋　　　2　　在籍　1000

【tbl3】
給与下限　給与上限　　税
pay_from　pay_to　tax
---------------------------------
0　　　　10000　　100
10000　20000　　200

↓
事業所(1)在籍者の8月分の支払情報をSelect文で取り出す。
(出勤日数＝tbl1の出勤＋遅刻)

社員名　出勤　欠勤　遅刻　時間　　給与　　税　　
-----------------------------------------------------------------
田中　　　2　　1　　0　　16　16000　200
佐藤　　　1　　0　　1　　　6　 6000　 100

回答No.3

kosukejlampnet · ベストアンサー

修正も含みます

SELECT
tbl1.sya_cd as 社員コード,
tbl1.sya_nm as 社員名,
SUM(IF(tbl1.status='出勤' OR tbl1.status='遅刻'), 1, 0) as 出勤,
SUM(IF(tbl1.status='欠勤'), 1, 0) as 欠勤,
SUM(IF(tbl1.status='遅刻'), 1, 0) as 遅刻,
SUM(tbl1.hours) as 時間,
SUM(tbl1.hours) * tbl2.pay as 給与,
(SELECT tax FROM tbl3 WHERE SUM(tbl1.hours) * tbl2.pay BETWEEN tbl3.pay_from AND tbl3.pay_to) as 税
from tbl1
INNER JOIN tbl2 ON tbl2.sya_cd=tbl1.sya_cd
WHERE tbl2.jig_cd=1
AND DATE_FORMAT(tbl1.date, '%Y%m')='201208'
GROUP BY DATE_FORMAT(tbl1.date, '%Y%m')

回答No.2

kosukejlampnet

SELECT
tbl1.sya_cd as 社員コード,
tbl1.sya_nm as 社員名,
SUM(IF(tbl1.status='出勤' OR tbl1.status='遅刻'), 1, 0) as 出勤,
SUM(IF(tbl1.status='欠勤'), 1, 0) as 欠勤,
SUM(IF(tbl1.status='遅刻'), 1, 0) as 遅刻,
SUM(tbl1.hours) as 時間,
tbl1.hours * tbl2.pay as 給与,
from tbl1
INNER JOIN tbl2 ON tbl2.sya_cd=tbl1.sya_cd
WHERE tbl2.jig_cd=1
AND DATE_FORMAT(tbl1.date, '%Y%m')='201208'
GROUP BY DATE_FORMAT(tbl1.date, '%Y%m')

※tbl3 の関係性が書かれていないのと、推測もつかないので税については省略

お礼

まだ試せてはいないですが、取り急ぎ御礼申し上げます。kosukejlampnetさん、ありがとうございます。

また、tbl3についての説明がなく申し訳ありません。
給与合計がtbl3の「下限」以上、「上限」未満であれば、その行の「税」を課す、という意味です。

回答No.1

kosukejlampnet

結合のSQLより、テーブル設計そのものについてアドバイスを求めたほうが、よろしいかと・・・。
プライマリキーを設定しましょう。
文字列のフィールドを結合キーにするわけにはいきませんから。
このままだと、同姓の人がいたら、大変なことになるよ。

補足

すみません、質問をデフォルメするために「田中」、「佐藤」などの姓で書いていますが、実際には「社員ID」などの主キーが設定されています。今回の場合、「同姓のものは存在しない」という想定のもと、社員名(Sya_Nm)を主キーとして想定していただければと思います。。