Works > 逆引きSQL比較 > MySQL > SQL
MySQL
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
■SQL
この項で使用するサンプルテーブルについて ▲TOP
日付型データをフォーマットして表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--YYYY-MM-DD
select date_format(now(),'%Y-%m-%d') as NOW;
--YYYY-MM-DD HH:MM:SS
select date_format(now(),'%Y-%m-%d %H:%i:%s') as NOW;
--YYYYMM
select date_format(now(),'%Y%m%d') as NOW;
日付の演算あれこれ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--1日後の日を求める（1日前なら'interval -1 day'にするかdate_sub）
select date_add(now(),interval 1 day);
--1月後の日を求める（1月前なら'interval -1 month'にするかdate_sub）
select date_add(now(),interval 1 month);
--日付間の日数（最も古い売上.販売日から何日経ったか）
(PEND.)
--日付間の月数（最も古い売上.販売日から何ヶ月経ったか）
(PEND.)
--日付間の年数（最も古い売上.販売日から何年経ったか）
(PEND.)
--日付の月末日を求める（関数はないので『翌月初日－１日』を求める）
select date_sub(date_add(date_format(now(),'%Y-%m-01'),interval 1 month),interval 1 day);
INSERT時にキーを自動的に連番で振るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
サンプルの販売実績テーブル（ksale）の主キー saleno は自動的に連番が振られるようになっています。
各RDBMSデータ作成用のスクリプトを参考にしてください。
--オートナンバー型の列を「主キーとして」定義しておく必要があります。
create table hoge
(id int UNSIGNED NOT NULL AUTO_INCREMENT ,
col int,
constraint PK_hoge primary key (id)
);
--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
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以上の人は「良」、それ以外は「可」と表示します。
select e.empnm , 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 join kemp e
on s.empno = e.empno
group by e.empnm;
クロス集計の方法は？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLE8以降及びMSSQL6.5以降では、クロス集計用にANSIのROLLUP,CUBE が使用できますが、エンドユーザーが求めているレイアウトとは違うような気がします（この認識って間違い？）。
いわゆる『Access のクロス集計クエリー』の実装を試みるなら、てっとり早いのは、複数のサブクエリーを使う事ですが、それでは列毎にSQL文が実行される事になるので、あまりにもパフォーマンスが悪すぎます。
以下の方法で実装すれば、サブクエリーを使用する方法よりは効率のよいSQLになります。但し、横列は固定になる点に注意してください。横列が不定の場合は、動的SQLなどを使用して一時表やビューを作る必要があるでしょう。
年を縦、月を横にした販売数量合計を表示します。
select extract(year from saledt) as "年",
nullif(sum(case when extract(month from saledt) = 1 then qty end),0) as "1月",
nullif(sum(case when extract(month from saledt) = 2 then qty end),0) as "2月",
nullif(sum(case when extract(month from saledt) = 3 then qty end),0) as "3月",
nullif(sum(case when extract(month from saledt) = 4 then qty end),0) as "4月",
nullif(sum(case when extract(month from saledt) = 5 then qty end),0) as "5月",
nullif(sum(case when extract(month from saledt) = 6 then qty end),0) as "6月",
nullif(sum(case when extract(month from saledt) = 7 then qty end),0) as "7月",
nullif(sum(case when extract(month from saledt) = 8 then qty end),0) as "8月",
nullif(sum(case when extract(month from saledt) = 9 then qty end),0) as "9月",
nullif(sum(case when extract(month from saledt) = 10 then qty end),0) as "10月",
nullif(sum(case when extract(month from saledt) = 11 then qty end),0) as "11月",
nullif(sum(case when extract(month from saledt) = 12 then qty end),0) as "12月"
from ksale
group by extract(year from 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;
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 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;
このページの先頭へ
MySQL
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
Works > 逆引きSQL比較 > MySQL > SQL