Works > 逆引きSQL比較 > Postgres > SQL
PostgreSQL
[ SQL ]
[ データベースの情報 ]
■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月前なら'-1 month'にすればよい）
select now() + '1 month';
--日付間の日数（最も古い売上.販売日から何日経ったか）
select now() - min(saledt) from ksale;
--日付間の月数（最も古い売上.販売日から何ヶ月経ったか）
(PEND.)
--日付間の年数（最も古い売上.販売日から何年経ったか）
select age(now(),min(saledt)) from ksale;
--日付の月末日を求める
(PEND.)
INSERT時にキーを自動的に連番で振るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
サンプルの販売実績テーブル（ksale）の主キー saleno は自動的に連番が振られるようになっています。
各RDBMSデータ作成用のスクリプトを参考にしてください。
--順序（SEQUENCE）オブジェクトを作成しておく必要があります。
create SEQUENCE hoge_seq start 1 increment 1 maxvalue 99999 cycle cache 1;
--INSERT時には以下の様に nextval を使って、明示的に順序をインクリメントする必要があります。
insert into hoge(col) values (nextval('hoge_seq'));
-- ※ つまり、オラクル同様、他のデータベースにあるようなオートナンバー型はありません。
同一テーブル内の別の行にある情報を結合して検索するには？ ▲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
and 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以上の人は「良」、それ以外は「可」と表示します。
以前のは結果が違っていたので少し修正しました。
（他のテーブルと結合する場合の振る舞いがMySQLとは違うようなので注意してください）
select e.empnm, ss.売上, ss.成績
from kemp e inner join
(
select s.empno, sum(s.qty) as "売上",
case when sum(s.qty) >= 200 then '優'
when sum(s.qty) < 200 and sum(s.qty) >= 100 then '良'
else '可'
end as "成績"
from ksale s
group by s.empno
) ss
on e.empno = ss.empno
order by e.empnm;
クロス集計の方法は？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLE8以降及びMSSQL6.5以降では、クロス集計用にANSIのROLLUP,CUBE が使用できますが、エンドユーザーが求めているレイアウトとは違うような気がします（この認識って間違い？）。
いわゆる『Access のクロス集計クエリー』の実装を試みるなら、てっとり早いのは、複数のサブクエリーを使う事ですが、それでは列毎にSQL文が実行される事になるので、あまりにもパフォーマンスが悪すぎます。
以下の方法で実装すれば、サブクエリーを使用する方法よりは効率のよいSQLになります。但し、横列は固定になる点に注意してください。横列が不定の場合は、動的SQLなどを使用して一時表やビューを作る必要があるでしょう。
年を縦、月を横にした販売数量合計を表示します。
select date_part('year',saledt) as "年",
nullif(sum(case when date_part('month',saledt) = 1 then qty end),0) as "1月",
nullif(sum(case when date_part('month',saledt) = 2 then qty end),0) as "2月",
nullif(sum(case when date_part('month',saledt) = 3 then qty end),0) as "3月",
nullif(sum(case when date_part('month',saledt) = 4 then qty end),0) as "4月",
nullif(sum(case when date_part('month',saledt) = 5 then qty end),0) as "5月",
nullif(sum(case when date_part('month',saledt) = 6 then qty end),0) as "6月",
nullif(sum(case when date_part('month',saledt) = 7 then qty end),0) as "7月",
nullif(sum(case when date_part('month',saledt) = 8 then qty end),0) as "8月",
nullif(sum(case when date_part('month',saledt) = 9 then qty end),0) as "9月",
nullif(sum(case when date_part('month',saledt) = 10 then qty end),0) as "10月",
nullif(sum(case when date_part('month',saledt) = 11 then qty end),0) as "11月",
nullif(sum(case when date_part('month',saledt) = 12 then qty end),0) as "12月"
from ksale
group by date_part('year',saledt);
最大値と等しいデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
顧客単位の最大販売数量を売ったスタッフの一覧を表示します。
select s.saledt,e.empnm,c.custnm,v.maxqty
from ksale s, kemp e, kcust c,
(select custno,max(qty) as maxqty from ksale group by custno) v
where s.custno = v.custno
and s.qty = v.maxqty
and s.empno = e.empno
and s.custno = c.custno
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;
ソートでNULL列の行の順番を上に(下に)するには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
社員マスタの取締役社長のデータには部署番号が入っていません。
ソート時にNULL列の行を明示的に上に(もしくは下に)表示します。
--Postgresの場合は、NULL列の行が一番下に表示されてしまうので、nullifで最小値に変えてソートする
select * from kemp
order by nullif(deptno,0);
--※8.0で、これ↑うまくいきませんでした。これ↓で代替。
select * from kemp
order by case when deptno is null then 0 else deptno end;
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
set price =
(select newp.price from knewprice newp
where newp.prodno = kprod.prodno
)
where exists
(select 'x' from knewprice newp
where newp.prodno = kprod.prodno
);
※新単価が商品マスタのすべての商品を満たす場合、exists（または in ）による
相関副問合わせは、不要です。
近似値を求めるには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
absを使って近似値を求める例です。
--条件に一番近い日付（前後は問わない）のデータ
select *
from ksale
where abs(saledt - to_date('2000-11-09','yyyy-mm-dd')) =
(select min(abs(saledt - to_date('2000-11-09','yyyy-mm-dd')))
from ksale
where saledt != to_date('2000-11-09','yyyy-mm-dd')
);
--条件に一番近い過去日付のデータ
select *
from ksale
where abs(saledt - to_date('2000-11-09','yyyy-mm-dd')) =
(select min(abs(saledt - to_date('2000-11-09','yyyy-mm-dd')))
from ksale
where saledt < to_date('2000-11-09','yyyy-mm-dd')
);
--条件に一番近い未来日付のデータ
select *
from ksale
where abs(saledt - to_date('2000-11-09','yyyy-mm-dd')) =
(select min(abs(saledt - to_date('2000-11-09','yyyy-mm-dd')))
from ksale
where saledt > to_date('2000-11-09','yyyy-mm-dd')
);
※言うまでもなく、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;
検索で正規表現は可能？ ▲TOP
MSSQL SSA Postgres
LIKE 検索時に正規表現を使用します。
--Postgresの正規表現は強力です。
--SQL Server系のようにLIKEの中で拡張的に使う、と言うよりは「正規表現直書き」という感じ
--になります。
--7.3以降、3種類に増えたパターンマッチング（通常のLIKE、SIMILAR TO、POSIX正規表現）のうち、
--POSIX正規表現の例をいくつか。
select 'あいうえおBo0約定' ~ 'う*[A-Z].[0-9][亜-龠]'; --TRUE
select chr(13) || chr(10) ~ '\r\n'; --TRUE
select chr(13) || chr(10) || 'aaaa' ~ '^(\r\n)'; --TRUE
select 'aaaa' || chr(13) || chr(10) ~ '^(\r\n)'; --FALSE
--実際に検索で使う時は、こんな感じになります ※LIKEは使いません
select * from kcust
where custnm ~ '[小-長]';
このページの先頭へ
PostgreSQL
[ SQL ]
[ データベースの情報 ]
Works > 逆引きSQL比較 > Postgres > SQL