解けずにおりますクエリの解をご教授下さい。

質問

以下定義のレコードを基準日毎に処理をして行きます。
処理の途中で変更フラグに"9"が出現した時、項目1が同一である
基準日～銘柄コード項目に付いて、直前レコードの値を取得したいのですが。

以下の場合ですと、アスタリスクの項目1の値を持つ最新のレコードは
アットマークのレコードになります。
従いまして"080613|1|001|CD0002"を取り出したいのですが。
ちなみに基準日～銘柄コードはキー項目の為、一意になっています。

いろいろ試しましたが、上手く取り出せません。
ご教授下さいます様、よろしくお願いいたします。

環境：SQL Server2005

基準日|変更フラグ|連番|銘柄コード|項目1
080612|1|001|CD0002|01010101
@ 080613|1|001|CD0002|01010101
080614|1|001|CD0001|11001100
080614|1|002|CD0002|11111111
080615|1|001|CD0001|10101010
* 080615|9|002|CD0002|01010101
080615|1|003|CD0002|01010100

回答No.1

chukenkenkou · ベストアンサー

>基準日～銘柄コード項目に付いて、直前レコードの値を取得したいのですが。
>以下の場合ですと、アスタリスクの項目1の値を持つ最新のレコードは
>アットマークのレコードになります。
>従いまして"080613|1|001|CD0002"を取り出したいのですが。

「直前」とか「最新」というのが曖昧ですが？
どちらも、@の行のことを指してますよね？

>ちなみに基準日～銘柄コードはキー項目の為、一意になっています

提示されたデータ例を見る限り、標準日＋連番だけで一意になるようですが？

曖昧な部分がありますが、「標準日＋連番だけで一意になる」と解釈し、SQL例を示します。
SQL Server 2005から実装された、Oracleでいう分析関数を使った例です。
もう少し効率的にできるのですが、曖昧な部分があるので取りあえずこの段階で提示しておきますので参考にしてください。

＜SQL例＞
select
y.*
from
(select
koumoku1
from t1
where henko='9') as x,
(select
*,
row_number() over(partition by koumoku1 order by kijunbi desc,renban desc) as rn
from t1) as y
where x.koumoku1=y.koumoku1
and rn=2
order by y.koumoku1

お礼

お礼が遅れまして、大変失礼致しました。

row_number～の箇所から以下のアイデアが思いつきました。

order by句で基準日,連番を新->旧になる様"desc"指定し、
select時に"top 1"とで最初の1件を取り出す。

見事に期待通りの結果が得られました。

恐らく、自分の回答をご覧になられて、
「それを求める質問だったのかよ。だったらその回答、とっくに思い付いていたよ」
と思われましたら、説明が下手でしたこと、申し訳御座いません。

しかし、そこを酌んで頂き、大変なヒントとなりました。

これからはもう少し、相手に伝わる様に心がけるように致します。

有難う御座いました。

回答No.3

plutoscp

質問者の内容とおり、以下のテーブルを作成しました。

CREATE TABLE [dbo].[対象テーブル](
[Seq] [bigint] IDENTITY(1,1) NOT NULL,
[基準日|変更フラグ|連番|銘柄コード|項目1] [varchar](50) NOT NULL,
)
INSERT INTO 対象テーブル
select '080612|1|001|CD0002|01010101'
union
select '080613|1|001|CD0002|01010101'
union
select '080614|1|001|CD0001|11001100'
union
select '080614|1|002|CD0002|11111111'
union
select '080615|1|001|CD0001|10101010'
union
select '080615|9|002|CD0002|01010101'
union
select '080615|1|003|CD0002|01010100'

CREATE FUNCTION CTE()
RETURNS @TEMP TABLE
(
基準日 varchar(6),
変更フラグ varchar(1),
連番 varchar(3),
銘柄コード varchar(6),
項目1 varchar(8)
)
AS
BEGIN
declare c1 cursor for
select
[基準日|変更フラグ|連番|銘柄コード|項目1] as str_A
from 対象テーブル
declare @wk_str varchar(50)
declare
@wk_基準日 varchar(6),
@wk_変更フラグ varchar(1),
@wk_連番 varchar(3),
@wk_銘柄コード varchar(6),
@wk_項目1 varchar(8)
open c1

fetch next from c1
into @wk_str

WHILE @@FETCH_STATUS = 0
begin

set @wk_基準日 = substring(@wk_str,1,6)
set @wk_変更フラグ = substring(@wk_str,8,1)
set @wk_連番 = substring(@wk_str,10,3)
set @wk_銘柄コード = substring(@wk_str,14,6)
set @wk_項目1 = substring(@wk_str,22,8)

insert into @TEMP(基準日, 変更フラグ, 連番, 銘柄コード, 項目1)
values (@wk_基準日, @wk_変更フラグ, @wk_連番, @wk_銘柄コード, @wk_項目1)

fetch next from c1
into @wk_str

end

close c1
DEALLOCATE c1

RETURN
END

※使い方↓とおり
select * from cte()

上記を利用すればクエリを作成し、参照できるかとおもいます。
ご参照ください。

お礼

お礼が遅れまして、大変失礼致しました。

No.1さんの回答からヒントを得まして、無事解決に至りました。

今後に役立つ内容が御座いましたので、勉強になりました。

有難う御座いました。

回答No.2

nfushi

では、私は別解を。

SELECT
MAX(A.基準日)AS 最新日,
A.変更フラグ,
A.連番,
A.銘柄コード,
A.項目1
FROM No4108525 A INNER JOIN
(
SELECT
B.基準日,
B.項目1
FROM NO4108525 B
WHERE B.変更フラグ = 9
)C ON A.項目1 = C.項目1
WHERE
A.基準日 < C.基準日
GROUP BY
A.連番,
A.変更フラグ,
A.銘柄コード,
A.項目1

お礼

お礼が遅れまして、大変失礼致しました。

こちらでも期待値が得られました。

No.1さんのご回答で先に解決してしまいました。

しかし、こちらのご回答も今後に役立つ回答でしたので、大いに勉強になりました。

説明が下手な中、丁寧にご回答下さいまして有難う御座いました。