oracle 最新日付との比較

質問

いま、同一商品番号の最新日付を利用した条件が
うまく作れず悩みながら色々試していますが、
うまくいきません。

やりたいことは、
1，AテーブルとBテーブルの
商品番号が同じ場合は除外します。

2，残されたデータから次はCテーブルの中にある
同一商品番号で、最新の日付が当月の場合はデータを残す。
つまり、前月以前のデータは除外となります。

1まではできています。
Select ＊ from aテーブル where
not exists (select 'X' from bテーブル where
a.商品番号 = b.商品番号)

この先上記sqlにつなげてmax関数くくった最新日付と
システム日付を年月のみで比較する等試みましたが、
求めた結果になりません。

上記sqlでビューを作成してといったことは、
今回やらずに一つのsqlのみでできないかと
試行錯誤です。

よきアドバイスございましたら、
宜しくお願い致します。

回答No.4

Siegrune · ベストアンサー

>最新の日付が当月の場合はデータを残す。
>つまり、前月以前のデータは除外となります。
別に最新にこだわる必要はなく（当月のデータが１件でもあれば最新じゃなくても条件成立のため）
補足の
Select ＊ from aテーブル a where
not exists (select 'X' from bテーブル b where
a.商品番号 = b.商品番号)
and
not exists (select 'X' from cテーブル where
(a.商品番号 = c.商品番号)
and /* ここから */
(select to_char(to_date(max(c.日付),'YYYYMM'),'YYYYMM')
,'YYYYMM')
< to_char(to_date(to_char(sysdate,'YYYYMM'),'YYYYMM')-1
,'YYYYMM')
の /* ここから */以下を、
to_char(to_date(c.日付,'YYYYMM'),'YYYYMM')
< to_char(to_date(to_char(sysdate,'YYYYMM'),'YYYYMM')-1,'YYYYMM')
)
としても取得できます。但し、負荷が高い、レスポンスが悪いかな。

and a.商品番号
not in
(select distinct 商品番号 from cテーブル
where trunc(日付,'MM')>=trunc(sysdate,'MM')
)
か

and
not exists
(select 'X' from cテーブル
where c.商品番号 = a.商品番号
and c.日付 = (select max(c2.日付) from cテーブル c2
where c2.商品番号 = c.商品番号)
and trunc(c.日付,'MM')>=trunc(sysdate,'MM')
)
あるいは
and
not exists
(select 'X' from cテーブル
where c.商品番号 = a.商品番号
and c.日付 = (select max(c2.日付) from cテーブル c2
where c2.商品番号 = c.商品番号
and trunc(c2.日付,'MM')>=trunc(sysdate,'MM')
)
か。
どれが早いかは、抽出された結果の商品番号の件数やaテーブルの件数等に依存しますので
良く分かりませんが。

お礼

レスポンスの例まで示していただきまして、ありがとうございました。
既に通常の抽出としてはうまくいっていたのですが、
レスポンスで問題がおきていました。

参考にさせていただきます。ありがとうございましたm(_ _)m

回答No.3

yamada_g

A,B,Cの3テーブルがあるということですよね？

最終的に取得したい項目が不明ですが、
select a.商品番号, max(c.日付) 最新日付
from (
select a.商品番号
from aテーブル a
where not exists (
select *
from bテーブル b
where a.商品番号 = b.商品番号)) a
inner join cテーブル c on (a.商品番号 = c.商品番号)
group by a.商品番号
having trunc(max(c.日付) , 'MM') = trunc(sysdate, 'MM');

みたいな感じでどうでしょうか。

お礼

inner joinでの結合はどうもテーブルの関係上
マテリアルビュー等を使用しているため非常に重いのが難点でした。
ありがとうございました。

回答No.2

bin-chan

> 1まではできています。
> 上記sqlでビューを作成してといったことは、今回やらずに
fromにselect文を書けます。1をfromに書けばSQLは１文。
ただし*で書かずに列を列挙、さらに全体を（）で囲む。

select 商品番号, 最新日付
from (
Select a.商品番号, max(c.日付) as 最新日付, from aテーブル where
not exists (select 'X' from bテーブル where
a.商品番号 = b.商品番号)
group by a.商品番号
) c
where
<dualから取得した日付とc.最新日付とをごにょごにょする>

お礼

ありがとうございます。
若干改造は必要のようでしたが、参考になりました！

補足

ご回答ありがとうございます。
試してみます。
結果は回答と共にお知らせさせていただきます。

回答No.1

bin-chan

> システム日付を年月のみで比較する等試みましたが、
比較部分をどのように記述してますか？日付はDate型じゃなのかな？

Date型なら文字列にしてしまうのが簡単かも。
select to_char( sysdate, 'YYYY/MM' ) from dual;

お礼

ありがとうございます。
抽出はなんとかできましたm(_ _)m

補足

目的は、削除リストを作成するためにSQLで削除対象となるデータを
抽出します。

SQLですとAテーブルとBテーブルに一致する場合は削除対象外としています。

Cは履歴のテーブルで同じ商品番号が複数存在します。
そこから一番新しい日付のものを取得したいです。
ブランクのものもあり、ブランクのものは当月のデータと
同一で削除対象外とします。

Select ＊ from aテーブル where
not exists (select 'X' from bテーブル where
a.商品番号 = b.商品番号)
and
not exists (select 'X' from cテーブル where
(a.商品番号 = c.商品番号)
and
(select to_char(to_date(max(c.日付),'YYYYMM'),'YYYYMM')
,'YYYYMM')
< to_char(to_date(to_char(sysdate,'YYYYMM'),'YYYYMM')-1
,'YYYYMM')

確か上記だったと思います。