Works > 逆引きSQL比較 > SSA > SQL
SSA
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
■SQL
この項で使用するサンプルテーブルについて ▲TOP
日付型データをフォーマットして表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--YYYY-MM-DD
select dateformat(now(),'YYYY-MM-DD') "NOW";
--YYYY-MM-DD HH:MM:SS
select dateformat(now(),'YYYY-MM-DD HH:MM:SS') "NOW";
--YYYYMM
select dateformat(now(),'YYYYMM') "NOW";
日付の演算あれこれ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--1日後の日を求める（1日前ならマイナスにすればよい）
select now() + 1;
select days(now(),1);
--1月後の日を求める（1月前ならマイナスにすればよい）
select dateadd(mm, 1, now());
select months(now(),1);
--日付間の日数（最も古い売上.販売日から何日経ったか）
select datediff(day, min(saledt), now()) from ksale;
select days(min(saledt), now()) from ksale;
select now() - min(saledt) from ksale;
--日付間の月数（最も古い売上.販売日から何ヶ月経ったか）
select datediff(mm, min(saledt), now()) from ksale;
select months(min(saledt), now()) from ksale;
--日付間の年数（最も古い売上.販売日から何年経ったか）
select datediff(yy, min(saledt), now()) from ksale;
select years(min(saledt), now()) from ksale;
--日付の月末日を求める（関数はないので『翌月初日－１日』を求める）
select cast(dateformat(dateadd(mm, 1, now()),'YYYY') || '-' ||
dateformat(dateadd(mm, 1, now()),'MM') || '-' || '01' as date)-1
INSERT時にキーを自動的に連番で振るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
サンプルの販売実績テーブル（ksale）の主キー saleno は自動的に連番が振られるようになっています。
各RDBMSデータ作成用のスクリプトを参考にしてください。
--オートナンバー型の列をIDENTITYまたは default AUTOINCREMENT を使って定義しておく必要があります。
create table hoge(id int IDENTITY , col int);
create table hoge(id int default AUTOINCREMENT , col int);
--INSERT時には、SQL文に、その列名を含まないようにします。
insert into hoge(col) values (1);
同一テーブル内の別の行にある情報を結合して検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
自己結合を使用します。
スタッフ名（社員.社員名）の横に、所属支店の支店長の名前（これも社員.社員名）を表示します。
select e1.empnm "スタッフ名" , e2.empnm "支店長名" from kemp e1 join kemp e2 on e1.deptno=e2.deptno
where e1.job = 'スタッフ'
and e2.job='支店長';
--または
select e1.empnm "スタッフ名" , e2.empnm "支店長名" from kemp e1, kemp e2
where 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 "スタッフ名", s.saledt "販売日" , s.qty "販売数量"
from kemp e left outer join ksale s on e.empno = s.empno
where e.job = 'スタッフ'
order by e.empno,s.saledt;
--または SYBASE 構文
select e.empnm "スタッフ名", s.saledt "販売日" , s.qty "販売数量" from kemp e, ksale s
where e.empno *= s.empno
and e.job = 'スタッフ'
order by e.empno,s.saledt;
SELECT文の中で条件分岐するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
販売数量合計が200以上の人は成績に「優」、100以上の人は「良」、それ以外は「可」と表示します。
--SSA5.5には SQL内で分岐制御する関数等は見当たりません。
--しかし、次の『クロス集計』で分岐させたいので、ちょ～簡便なDECOLITE関数を作っておきます。
create function decolite(in inval1 varchar(255),in inval2 varchar(255),in inval3 varchar(255),in inval4 varchar(255))
returns varchar(255)
begin
declare ret varchar(255);
if inval1 = inval2 then
select inval3 into ret;
else
select inval4 into ret;
end if;
return ret;
end;
select decolite(1,2,'真','偽');
クロス集計の方法は？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLE8以降及びMSSQL6.5以降では、クロス集計用にANSIのROLLUP,CUBE が使用できますが、エンドユーザーが求めているレイアウトとは違うような気がします（この認識って間違い？）。
いわゆる『Access のクロス集計クエリー』の実装を試みるなら、てっとり早いのは、複数のサブクエリーを使う事ですが、それでは列毎にSQL文が実行される事になるので、あまりにもパフォーマンスが悪すぎます。
以下の方法で実装すれば、サブクエリーを使用する方法よりは効率のよいSQLになります。但し、横列は固定になる点に注意してください。横列が不定の場合は、動的SQLなどを使用して一時表やビューを作る必要があるでしょう。
年を縦、月を横にした販売数量合計を表示します。
--前述の『SELECT文の中で条件分岐』で作成したDECOLITE関数を使用します。
select year(saledt) "年",
isnull(sum(decolite(month(saledt),1,qty,0)),0) "1月",
isnull(sum(decolite(month(saledt),2,qty,0)),0) "2月",
isnull(sum(decolite(month(saledt),3,qty,0)),0) "3月",
isnull(sum(decolite(month(saledt),4,qty,0)),0) "4月",
isnull(sum(decolite(month(saledt),5,qty,0)),0) "5月",
isnull(sum(decolite(month(saledt),6,qty,0)),0) "6月",
isnull(sum(decolite(month(saledt),7,qty,0)),0) "7月",
isnull(sum(decolite(month(saledt),8,qty,0)),0) "8月",
isnull(sum(decolite(month(saledt),9,qty,0)),0) "9月",
isnull(sum(decolite(month(saledt),10,qty,0)),0) "10月",
isnull(sum(decolite(month(saledt),11,qty,0)),0) "11月",
isnull(sum(decolite(month(saledt),12,qty,0)),0) "12月"
from ksale
group by year(saledt);
最大値と等しいデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
顧客単位の最大販売数量を売ったスタッフの一覧を表示します。
--SSA5.5では FROM句のインラインビュー は使用できません。
--FROM句のインラインビューが使えない場合（相関サブクエリー）
select s.saledt,e.empnm,c.custnm,s.qty from ksale s, kemp e, kcust c
where s.empno = e.empno
and s.custno = c.custno
and 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;
--※SSA5.5では FROM句のインラインビュー は使用できません。
--インラインビューの書けないバージョンでは、ちょっとかっこ悪いですが、
--以下の方法で実装可能。
select e.empnm,s1.qty,e.empno,s1.empno
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(distinct s2.qty) <= 3)
and s1.empno = e.empno
group by e.empno,s1.empno,e.empnm,s1.qty
order by s1.empno,s1.qty desc;
ソートでNULL列の行の順番を上に(下に)するには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
社員マスタの取締役社長のデータには部署番号が入っていません。
ソート時にNULL列の行を明示的に上に(もしくは下に)表示します。
--SSAの場合は、NULL列の行が一番上に表示されてしまうので、ISNULLで最大値に変えてソートする
select * from kemp
order by isnull(deptno,9999);
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 , knewprice new
set kprod.price = new.price
where kprod.prodno = new.prodno;
※古いバージョンでも、このANSIスタイルができるんですね、SSAは。So kool!!
近似値を求めるには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
absを使って近似値を求める例です。
--条件に一番近い日付（前後は問わない）のデータ
select *
from ksale
where abs(datediff(day,saledt,cast('2000-11-09' as datetime))) =
(select min(abs(datediff(day,saledt,cast('2000-11-09' as datetime))))
from ksale
where saledt != cast('2000-11-09' as datetime)
);
--条件に一番近い過去日付のデータ
select *
from ksale
where abs(datediff(day,saledt,cast('2000-11-09' as datetime))) =
(select min(abs(datediff(day,saledt,cast('2000-11-09' as datetime))))
from ksale
where saledt < cast('2000-11-09' as datetime)
);
--条件に一番近い未来日付のデータ
select *
from ksale
where abs(datediff(day,saledt,cast('2000-11-09' as datetime))) =
(select min(abs(datediff(day,saledt,cast('2000-11-09' as datetime))))
from ksale
where saledt > cast('2000-11-09' as datetime)
);
※言うまでもなく、min を max にすれば、「条件に最も遠い」データ
が求まります。
※ここでは日付の近似値を検索する例を挙げていますが、上記のように、
「比較対照と条件の差」が求められるのであれば、min(abs(比較対照 - 条件))
を検索条件にして、様々なデータ型の近似値が求められます。
グルーピングした時の合計や件数を条件にするには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
「グループ単位での上位ｎ件の求め方」でも使用しているように、
「GROUP BY でグルーピングした後の」合計や件数を条件として使いたい場合があります。
WHERE句を使って、この要件を満たすには、インラインビューを使用すれば
（それが使用可能なRDBMSであれば）可能ですが、
もっと効率的な方法としては、HAVING COUNT を使用します。
--SSA5.5では FROM句のインラインビュー は使用できません。
--3件以上の売上がある社員の社員番号は？（HAVING COUNT）
select empno,count(*)
from ksale
group by empno
having count(*) >= 3
order by count(*) desc;
検索で正規表現は可能？ ▲TOP
MSSQL SSA Postgres
LIKE 検索時に正規表現を使用します。
--以下のような正規表現が可能です。詳しい人はいろいろ試してみてください。
--※SSAでは、英字の大文字・小文字の区別ができませんでした。
select col from hoge
--where col like '%[A-Z][0-9]%' --英大文字に数字が連結している
--where col like '%[^A-Z][a-z]%' --1文字目が英大文字でなく、2文字目以降が英小文字
--where col like '%[ぁ-ん]%' --ひらがなを含む
--where col like '%[ァ-ン]%' --全角カタカナを含む
--where col like '%[亜-龠]%' --漢字を含む
このページの先頭へ
SSA
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
Works > 逆引きSQL比較 > SSA > SQL