Works > 逆引きSQL比較 > Jet > SQL
Jet (Access MDB)
[ SQL ]
[ データベースの情報 ]
■SQL
Jet(Access MDB)特有の留意点 ▲TOP
「Access の逆引きもお願いします」と言う、ご要望を何通か、いただきました。
個人的には、SQLエンジンとしての Jet について書きたい気持ちはあったのですが、
これまで作らなかった理由としては、Access をどのようなアプローチで使うかによって、
当サイトが有用となるかどうかが大きく分かれるからです。
WebアプリやC/S型のアプリのデータ格納場所としてのみ、Jet を使う局面は、
確かにあるとは思います。
（mdb を複数ユーザーから同時アクセスし、
業務アプリのデータベースとして使う事については、「好ましくない」
と云った議論がありますが、ここで、その事を論じるつもりはありません）
ただ、一般的に、Access を使う、と言う事は、
「フォーム、レポート、マクロ、VBA を使用したアプリケーションの作成」
が主だったものかと思われます。
「逆引きSQL比較」は、文字通り、SQL に注力しているコーナーであり、
フォーム、レポート、マクロ、VBA と云った「Access の使い方」
全般について述べる事は本筋ではありません。
（そもそも、その辺については詳しくもないですし。
「Access の使い方」全般については、有用なサイトや ML が、
数多く存在します）
と言う訳で、ここでは、「あくまでも JetSQL にのみ、拘泥」して、
お話しを進めさせていただく事とします。
例えば、テーブルの列の一覧などは、Access の場合、
ディクショナリから導出する事はできず、VBA を使う必要があるので、
ここでは述べません。
SQLの構文についても「Access以外のアプリケーションからSQLを実行」
する事を前提として書いていますので、その辺り、ご了承ください。
この項で使用するサンプルテーブルについて ▲TOP
日付型データをフォーマットして表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--YYYY-MM-DD
select format(now(),'yyyy-mm-dd') as NOW
--YYYY-MM-DD HH:MM:SS
select format(now(),'yyyy-mm-dd hh:m:ss') as NOW
--YYYYMM
select format(now(),'yyyymmdd') as NOW
日付の演算あれこれ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--1日後の日を求める（1日前ならマイナスにすればよい）
select now()+1
--1月後の日を求める（1月前ならマイナスにすればよい）
select dateadd('m', 1, now())
--日付間の日数（最も古い売上.販売日から何日経ったか）
select datediff('d', min(saledt), now()) from ksale
--日付間の月数（最も古い売上.販売日から何ヶ月経ったか）
select datediff('m', min(saledt), now()) from ksale
--日付間の年数（最も古い売上.販売日から何年経ったか）
select datediff('yyyy', min(saledt), now()) from ksale
--日付の月末日を求める（関数はないので『翌月初日－１日』を求める）
select CDate(format(dateadd('m', 1, now()),'yyyy-mm-01'))-1
INSERT時にキーを自動的に連番で振るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
サンプルの販売実績テーブル（ksale）の主キー saleno は自動的に連番が振られるようになっています。
各RDBMSデータ作成用のスクリプトを参考にしてください。
--オートナンバー型の列を定義しておく必要があります。
create table hoge(id AUTOINCREMENT , col int)
--INSERT時には、SQL文に、その列名を含まないようにします。
insert into hoge(col) values (1)
同一テーブル内の別の行にある情報を結合して検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
自己結合を使用します。
スタッフ名（社員.社員名）の横に、所属支店の支店長の名前（これも社員.社員名）を表示します。
select e1.empnm as スタッフ名 , e2.empnm as 支店長名
from kemp e1 inner join kemp e2 on e1.deptno=e2.deptno
where e1.job = 'スタッフ'
and e2.job='支店長'
結合条件に合致しなくてもすべての行を表示させるには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
外部結合を使用します。
始めに整理しておくと、ここで書いている外部結合の構文には
left outer join ：ANSI（ORACLE 9i以降,MSSQL,SSA,Jet）
外部結合演算子(+)：ORACLE 8i以下
外部結合演算子 * ：MSSQL,SSA（SYBASE SQL Server から引き継がれている構文）
の3種類があります。
個人的には SYBASE の方式が最も理解しやすいと思います。
『*（ワイルドカード）のある側はすべて』
という風に覚えられるからです。ORACLE はその逆と覚えればよいでしょう。
以下は、社員単位の販売実績を表示します（ただし販売実績のないスタッフも名前だけは表示する）。
select e.empnm as スタッフ名, s.saledt as 販売日 , s.qty as 販売数量
from kemp e left outer join ksale s on e.empno = s.empno
where e.job = 'スタッフ'
order by e.empno,s.saledt
SELECT文の中で条件分岐するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
販売数量合計が200以上の人は成績に「優」、100以上の人は「良」、それ以外は「可」と表示します。
select e.empnm , sum(s.qty) as 売上,
Switch(sum(s.qty) >= 200, '優', sum(s.qty) < 200 and sum(s.qty) >= 100, '良', True, '可') as 成績
from ksale s inner join kemp e
on s.empno = e.empno
group by e.empnm
※IIF関数の入れ子でも可能
クロス集計の方法は？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLE8以降及びMSSQL6.5以降では、クロス集計用にANSIのROLLUP,CUBE が使用できますが、エンドユーザーが求めているレイアウトとは違うような気がします（この認識って間違い？）。
いわゆる『Access のクロス集計クエリー』の実装を試みるなら、てっとり早いのは、複数のサブクエリーを使う事ですが、それでは列毎にSQL文が実行される事になるので、あまりにもパフォーマンスが悪すぎます。
以下の方法で実装すれば、サブクエリーを使用する方法よりは効率のよいSQLになります。但し、横列は固定になる点に注意してください。横列が不定の場合は、動的SQLなどを使用して一時表やビューを作る必要があるでしょう。
年を縦、月を横にした販売数量合計を表示します。
--と言う事で、これはAccessの得意とするところですね
transform sum(qty)
select year(saledt) as 年, sum(qty) as 合計
from ksale
group by year(saledt)
pivot month(saledt)
最大値と等しいデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
顧客単位の最大販売数量を売ったスタッフの一覧を表示します。
select s.saledt,e.empnm,s.qty
from (ksale s inner join kemp e on s.empno = e.empno)
inner join kcust c on s.custno = c.custno
where exists
(select custno,max(qty) from ksale
where custno = s.custno
group by custno
having max(qty)= s.qty
)
order by 1,2
AテーブルにあってBテーブルにないデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
売上テーブルに存在しない社員の一覧を表示します。
select e.empnm from kemp e
where not exists
(select 'X' from ksale s
where s.empno = e.empno
)
『グループ単位での』上位ｎ件を検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLEであれば 8i以降、MSSQLであれば 2005以降で、RANKなどの分析関数が使用可能ですが、以下の例は、ANSIエントリーレベルで実現でき、大抵のRDBMSで流用できます。
※
河端善博さんのブログにおいて、この辺りの事が議論されています。
より高速で効率的な例が載っていますので、ご覧あれ。
社員単位で、販売数量の大きかった順に最大3件ずつ（売上が3件に満たない場合もある）
売上一覧を表示します。
（＝ある人が最も沢山売ったときの数量はいくつか？人単位で上位３つを示せ）
--前に書いていたのがかなり結果がおかしかったので変えました
select e.empnm,v.qty
from
(
select distinct s1.empno,s1.qty
from ksale s1
where qty <= (select max(s2.qty)
from ksale s2
where s2.empno = s1.empno
and s2.qty >= s1.qty
having count(distinct s2.qty) <= 3)
) v ,
kemp e
where v.empno = e.empno
order by v.empno,v.qty desc
--※と書きましたが、実はJetでは having count(distinct col) が使用できませんでした。
--以下は以前に書いたものですが、結果は不完全です。どのように実装するか考え中。
select e.empnm,s1.qty
from ksale s1, kemp e
where qty <= (select max(s2.qty)
from ksale s2
where s2.empno = s1.empno
and s2.qty >= s1.qty
having count(s2.qty) <= 3)
and s1.empno = e.empno
order by s1.empno,s1.qty desc
ソートでNULL列の行の順番を上に(下に)するには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
社員マスタの取締役社長のデータには部署番号が入っていません。
ソート時にNULL列の行を明示的に上に(もしくは下に)表示します。
--JetSQLの場合は、NULL列の行が一番上に表示されてしまうので、ISNULLで最大値に変えてソートする
select * from kemp
order by iif(isnull(deptno),9999,deptno)
Aテーブルの列の内容でBテーブルを更新するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
新単価テーブルに該当する商品マスタについて、新しい単価に更新します。
--新単価テーブル作成
create table knewprice
(prodno int, --商品番号
price int --単価
);
--新単価を追加する
insert into knewprice values (1,1200);
insert into knewprice values (2,1300);
--新単価テーブルの新しい単価で商品マスタの単価を更新（該当しないものはそのまま）
update kprod inner join knewprice on kprod.prodno = knewprice.prodno
set kprod.price = knewprice.price
近似値を求めるには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
absを使って近似値を求める例です。
--条件に一番近い日付（前後は問わない）のデータ
select *
from ksale
where abs(datediff('d',saledt,cDate('2000-11-09'))) =
(select min(abs(datediff('d',saledt,cDate('2000-11-09'))))
from ksale
where saledt <> cDate('2000-11-09')
)
--条件に一番近い過去日付のデータ
select *
from ksale
where abs(datediff('d',saledt,cDate('2000-11-09'))) =
(select min(abs(datediff('d',saledt,cDate('2000-11-09'))))
from ksale
where saledt < cDate('2000-11-09')
)
--条件に一番近い未来日付のデータ
select *
from ksale
where abs(datediff('d',saledt,cDate('2000-11-09'))) =
(select min(abs(datediff('d',saledt,cDate('2000-11-09'))))
from ksale
where saledt > cDate('2000-11-09')
)
※言うまでもなく、min を max にすれば、「条件に最も遠い」データが求まります。
※ここでは日付の近似値を検索する例を挙げていますが、上記のように、
「比較対照と条件の差」が求められるのであれば、min(abs(比較対照 - 条件))
を検索条件にして、様々なデータ型の近似値が求められます。
グルーピングした時の合計や件数を条件にするには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
「グループ単位での上位ｎ件の求め方」でも使用しているように、
「GROUP BY でグルーピングした後の」合計や件数を条件として使いたい場合があります。
WHERE句を使って、この要件を満たすには、インラインビューを使用すれば
（それが使用可能なRDBMSであれば）可能ですが、
もっと効率的な方法としては、HAVING COUNT を使用します。
--3件以上の売上がある社員の社員番号は？（インラインビュー）
select v.*
from
(
select empno,count(*) as cnt
from ksale
group by empno
) as v
where v.cnt >= 3
order by v.cnt desc;
--3件以上の売上がある社員の社員番号は？（HAVING COUNT）
select empno,count(*)
from ksale
group by empno
having count(*) >= 3
order by count(*) desc;
このページの先頭へ
Jet (Access MDB)
[ SQL ]
[ データベースの情報 ]
Works > 逆引きSQL比較 > Jet > SQL