(cache) Oracle Technology Network (OTN) Japan - 掲示板 : クロス集計の応用？ ...

Oracle Technology Network (OTN) Japan - 掲示板 » テクノロジー » プログラミング一般

スレッド: クロス集計の応用？

ようこそ、ゲストさん

ログイン

掲示板検索 / ランキング

オンライン便利サービス

ヘルプ / 利用規約

このスレッドに返信する

スレッド一覧へ

	返信数: 2 - ページ数: 1 - 最新投稿 : 2004/02/05 12:45 最新投稿者: tanax1967 - スレッド表示形式:

tanax1967

投稿数: 12
登録日時: 02/10/30

クロス集計の応用？
投稿時刻: 2004/02/05 10:29

返信

次の３つのテーブルからデータを取得して、
毎月のシフト表を作成したいと考えています。

Staff（社員マスタ）
StaffNo VARCHAR2(8)
StaffName VARCHAR2(20)

Reservation（仕事情報）
ReservationNo NUMBER(6,0)
Workaday DATE
StartTime DATE
EndTime DATE

Shift（シフト情報）
ReservationNo NUMBER(6,0)
StaffNo VARCHAR2(8)

シフト表作成クエリ
SELECT s.StaffNo, s.StaffName,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'01',1,0)) D01,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'02',1,0)) D02,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'03',1,0)) D03,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'04',1,0)) D04,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'05',1,0)) D05,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'06',1,0)) D06,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'07',1,0)) D07,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'08',1,0)) D08,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'09',1,0)) D09,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'10',1,0)) D10,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'11',1,0)) D11,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'12',1,0)) D12,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'13',1,0)) D13,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'14',1,0)) D14,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'15',1,0)) D15,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'16',1,0)) D16,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'17',1,0)) D17,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'18',1,0)) D18,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'19',1,0)) D19,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'20',1,0)) D20,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'21',1,0)) D21,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'22',1,0)) D22,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'23',1,0)) D23,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'24',1,0)) D24,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'25',1,0)) D25,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'26',1,0)) D26,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'27',1,0)) D27,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'28',1,0)) D28,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'29',1,0)) D29,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'30',1,0)) D30,
SUM(DECODE(TO_CHAR(r.Workaday,'DD'),'31',1,0)) D31
FROM Staff s, Shift sft, Reservation r
WHERE s.StaffNo = sft.StaffNo
AND sft.ReservationNo = r.ReservationNo
AND TO_CHAR(r.Workaday,'YYYY/MM')='2004/02'
GROUP BY s.StaffNo, s.StaffName
ORDER BY s.StaffNo;

上記クエリで、とりあえずシフト表は出来るのですが、
さらに条件を付け加えて、

TO_CHAR(EndTime,'HH24:MI')<='13:30' ならば '△'
TO_CHAR(StartTime,'HH24:MI')>'12:00' ならば '▽'
それ以外は'○'

という感じで、場合分けをしたいと思います。
クエリだとかなり複雑になるでしょうか？
PL/SQLで作ったほうがよいでしょうか？
Oracle9i(9.2.0.1.0)です。

スマートな解決方法がありましたら御教授下さい。
よろしくお願いします。

ushitaki

投稿数: 7,079
登録日時: 98/10/30

RE:クロス集計の応用？
投稿時刻: 2004/02/05 10:53

tanax1967 さんへの返信です。

返信

現在 1,0 で表示しているものを△▽○−と
表示するレベルで良いのならば
先に、早番、遅番、普通のクエリを作ってから
横展開してはいかがでしょうか。

case式と decode式が混じっておりますが
下記のような感じ。

SELECT StaffNo, StaffName,
nvl(max(DECODE(DD,'01',pattern,null)),'−') D01,
nvl(max(DECODE(DD,'02',pattern,null)),'−') D02,
nvl(max(DECODE(DD,'03',pattern,null)),'−') D03,
nvl(max(DECODE(DD,'04',pattern,null)),'−') D04,
nvl(max(DECODE(DD,'05',pattern,null)),'−') D05,
-- (中略)
nvl(max(DECODE(DD,'28',pattern,null)),'−') D28,
nvl(max(DECODE(DD,'29',pattern,null)),'−') D29,
nvl(max(DECODE(DD,'30',pattern,null)),'−') D30,
nvl(max(DECODE(DD,'31',pattern,null)),'−') D31
from
(select s.StaffNo, s.StaffName,
to_char(r.Wrokday,'DD') dd,
case when TO_CHAR(r.EndTime,'HH24:MI')<='13:30'
then '△'
when TO_CHAR(r.StartTime,'HH24:MI')>'12:00'
then '▽'
else '○'
end pattern
FROM Staff s, Shift sft, Reservation r
WHERE s.StaffNo = sft.StaffNo
AND sft.ReservationNo = r.ReservationNo
AND TO_CHAR(r.Workaday,'YYYY/MM')='2004/02'
) v
GROUP BY StaffNo, StaffName
ORDER BY StaffNo;

なお、Reservation r は ReservationNo,Workaday で
Unique と言う前提で作成してみました。
(休みの場合はレコードなし)
なので max は該当日付のレコードがあれば△▽○のどれか
無ければ null となります。

tanax1967

投稿数: 12
登録日時: 02/10/30

RE[1]:クロス集計の応用？
投稿時刻: 2004/02/05 12:45

ushitaki さんへの返信です。

返信

クエリでIF..THEN..ELSEと同様のことが出来るんですね。
恥ずかしながら初めて知りました。
SQLリファレンスも「式」の部分は飛ばしてました…。

これで解決出来そうです。
ありがとうございました！

ページ数: 1

スレッド一覧へ


ウェブサイトのご使用条件 \| 個人情報保護基本方針/情報保護基本方針