Works > 逆引きSQL比較 > MSSQL > SQL
MSSQL
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
■SQL
この項で使用するサンプルテーブルについて ▲TOP
日付型データをフォーマットして表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--YYYY-MM-DD
select convert(varchar(10),getdate(),120) "NOW"
--YYYY-MM-DD HH:MM:SS
select convert(varchar(20),getdate(),120) "NOW"
--YYYYMM
select convert(varchar(6),getdate(),112) "NOW"
※スタイル(第3引数)で表示される書式は環境によって異なります。
datepartやdatename関数の使用も考慮してください。
日付の演算あれこれ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--1日後の日を求める（1日前ならマイナスにすればよい）
select getdate() + 1
--1月後の日を求める（1月前ならマイナスにすればよい）
select dateadd(mm, 1, getdate())
--日付間の日数（最も古い売上.販売日から何日経ったか）
select datediff(day, min(saledt), getdate()) from ksale
--日付間の月数（最も古い売上.販売日から何ヶ月経ったか）
select datediff(mm, min(saledt), getdate()) from ksale
--日付間の年数（最も古い売上.販売日から何年経ったか）
select datediff(yyyy, min(saledt), getdate()) from ksale
--日付の月末日を求める（関数はないので『翌月初日－１日』を求める）
select cast(datename(year,dateadd(mm, 1, getdate())) + '-' +
datename(month,dateadd(mm, 1, getdate())) + '-' + '01' as datetime)-1
INSERT時にキーを自動的に連番で振るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
サンプルの販売実績テーブル（ksale）の主キー saleno は自動的に連番が振られるようになっています。
各RDBMSデータ作成用のスクリプトを参考にしてください。
--オートナンバー型の列をIDENTITYを使って定義しておく必要があります。
create table hoge(id int IDENTITY , 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='支店長'
--または（※6.5より古いバージョンでは上記構文はエラーになるのでこちらで）
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 構文（※6.5より古いバージョンでは上記構文はエラーになるのでこちらで）
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以上の人は「良」、それ以外は「可」と表示します。
select e.empnm , sum(s.qty) "売上",
case when sum(s.qty) >= 200 then '優'
when sum(s.qty) < 200 and sum(s.qty) >= 100 then '良'
else '可'
end "成績"
from ksale s, kemp e
where 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 year(saledt) "年",
isnull(sum(case when month(saledt) = 1 then qty end),0) "1月",
isnull(sum(case when month(saledt) = 2 then qty end),0) "2月",
isnull(sum(case when month(saledt) = 3 then qty end),0) "3月",
isnull(sum(case when month(saledt) = 4 then qty end),0) "4月",
isnull(sum(case when month(saledt) = 5 then qty end),0) "5月",
isnull(sum(case when month(saledt) = 6 then qty end),0) "6月",
isnull(sum(case when month(saledt) = 7 then qty end),0) "7月",
isnull(sum(case when month(saledt) = 8 then qty end),0) "8月",
isnull(sum(case when month(saledt) = 9 then qty end),0) "9月",
isnull(sum(case when month(saledt) = 10 then qty end),0) "10月",
isnull(sum(case when month(saledt) = 11 then qty end),0) "11月",
isnull(sum(case when month(saledt) = 12 then qty end),0) "12月"
from ksale
group by year(saledt)
最大値と等しいデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
顧客単位の最大販売数量を売ったスタッフの一覧を表示します。
--FROM句のインラインビューが使える場合（6.5以降）
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
--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;
ソートでNULL列の行の順番を上に(下に)するには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
社員マスタの取締役社長のデータには部署番号が入っていません。
ソート時にNULL列の行を明示的に上に(もしくは下に)表示します。
--MSSQLの場合は、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 --単価
)
go
--新単価を追加する
insert into knewprice values (1,1200)
insert into knewprice values (2,1300)
go
--新単価テーブルの新しい単価で商品マスタの単価を更新（該当しないものはそのまま）
update kprod
set price = knewprice.price
from knewprice inner join kprod p on knewprice.prodno = p.prodno
go
※更新対象のテーブルに別名を付けるとエラーになります。
近似値を求めるには？ ▲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 を使用します。
--3件以上の売上がある社員の社員番号は？（インラインビュー）
select v.*
from
(
select empno,count(*) cnt
from ksale
group by empno
) 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 検索時に正規表現を使用します。
--以下のような正規表現が可能です。詳しい人はいろいろ試してみてください。
--※MSDE7.0では、英字の大文字・小文字の区別、ひらがな・カタカナの区別ができませんでした。
-- （確かインストール時の設定によるものだったような…）
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 '%[亜-龠]%' --漢字を含む
列の内容を16進（ヘキサダンプ）で表示するには？ ▲TOP
ORACLE MSSQL
列の内容を16進で表示します。
select convert(binary,'予定表' + char(10))
このページの先頭へ
MSSQL
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
Works > 逆引きSQL比較 > MSSQL > SQL