トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
5-30 特定レコードの、前のデータを取得
SQLパズル
htmlTableテーブル
ID name page created
-- ---- ------ -------------------
1 田中 1.html 2004-08-10 10:00:00
2 田中 2.html 2004-08-10 10:10:00
3 田中 4.html 2004-08-10 10:20:00
4 加藤 0.html 2004-08-10 10:00:00
5 加藤 2.html 2004-08-10 10:15:00
6 加藤 3.html 2004-08-10 10:20:00
7 村上 1.html 2004-08-10 10:00:00
8 村上 2.html 2004-08-10 10:10:00
9 村上 3.html 2004-08-10 10:30:00
nameごとに、pageの2.htmlの、一つ前の時間にあたるレコードを出力する。
出力結果
ID name page created
-- ---- ------ ------------------
1 田中 1.html 2004/8/10 10:00:00
4 加藤 0.html 2004/8/10 10:00:00
7 村上 1.html 2004/8/10 10:00:00
データ作成スクリプト
create Table htmlTable(
ID number(1),
name char(4),
page char(6),
created date);
insert into htmlTable values(1,'田中','1.html',to_date('2004-08-10 10:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(2,'田中','2.html',to_date('2004-08-10 10:10:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(3,'田中','4.html',to_date('2004-08-10 10:20:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(4,'加藤','0.html',to_date('2004-08-10 10:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(5,'加藤','2.html',to_date('2004-08-10 10:15:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(6,'加藤','3.html',to_date('2004-08-10 10:20:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(7,'村上','1.html',to_date('2004-08-10 10:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(8,'村上','2.html',to_date('2004-08-10 10:10:00','YYYY-MM-DD HH24:MI:SS'));
insert into htmlTable values(9,'村上','3.html',to_date('2004-08-10 10:30:00','YYYY-MM-DD HH24:MI:SS'));
commit;
SQL
--■■■サブクエリを使用する方法(createdが、2.htmlのcreatedの一つ前かチェック)■■■
select ID,name,page,to_char(created,'FMYYYY/MM/DD FMHH24:MI:SS') as created
from htmlTable a
where created = (select max(b.created) from htmlTable b
where b.name=a.name
and b.created < (select c.created from htmlTable c
where c.name= b.name
and c.page= '2.html' ))
order by ID;
--■■■サブクエリを使用する方法(次のpageが2.htmlかチェック)■■■
select ID,name,page,to_char(created,'FMYYYY/MM/DD FMHH24:MI:SS') as created
from htmlTable a
where (select b.page from htmlTable b
where b.name = a.name
and b.created = (select min(c.created) from htmlTable c
where c.name= b.name
and c.created > a.created)) = '2.html';
--■■■分析関数を使用する方法(createdが、2.htmlのcreatedの一つ前かチェック)■■■
select a.ID,a.name,a.page,to_char(a.created,'FMYYYY/MM/DD FMHH24:MI:SS') as created
from htmlTable a,
(select name,page,
Lag(created) over (partition by name order by created) as LagDate
from htmlTable) b
where a.name=b.name
and b.page='2.html'
and a.created = b.LagDate
order by ID;
--■■■分析関数を使用する方法(次のpageが2.htmlかチェック)■■■
select ID,name,page,to_char(created,'FMYYYY/MM/DD FMHH24:MI:SS') as created
from (select ID,name,page,created,
Lead(page) over (partition by name order by created) as LeadPage
from htmlTable)
where LeadPage = '2.html'
order by ID;
解説
サブクエリを使用する方法(createdが、2.htmlのcreatedの一つ前かチェック)では、
nameが同じで、'2.html'のcreatedよりも、createdが小さい中で、最大のcreatedと等しいことを、
where句の条件としてます。
次のpageが2.htmlかチェックする方法や、分析関数を使用する方法もあります。