この記事を読むのに必要な時間は約 7 分です。
このページではSQL文を書く上で上級者向けのテクニックです。
と言っても慣れてしまえばたいしたことはありません。大事な事は
という、その事実に気づくところです。
こういうのは普通だれも教えてくれないですからね。
SQL学習サイトオープン
SQLが自習できるサイトをオープンしました！！
そのなも・・・
初心者向けSQL学習サイト
初学者はご活用ください。
( *´艸｀)
上から２番目を求める事の難しさ
集合関数での限界
上から１番目を求める！
といった内容であれば真っ先に思いつくのが集合関数のMAXですね。これを使えば１番大きな値を求める事ができます。
そう
１番上の値しかだめなんですね・・・・・
また、以下のような表があったとします。
集合関数のMAXで求めれるのは
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
くだもの → 5000円
さかな → 10000円
おにく → 500円
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
です。
でも、みんなが知りたいのって
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
くだもの → メロン（5000円）
さかな → くえ（10000円）
おにく → うま（500円）
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
って情報ですよね。
副問い合わせ等を使えば求められますが、書き方が非常にややこしくなります。
そこで、今回お伝えする上級テクニックは以前説明した 以下の二つの内容を利用する事になります。
その５）SQLテクニック 番号を振る（ROW_NUMBER、RANK、DENSE_RANK）
その２）SQLテクニック Selectした結果をSelectする
row_numberとSelectした結果をSelectするやり方で上から２番目を求める。
エクセルで作ったサンプルの方が分かりやすそうなのでそのままテーブルも作ってみました。
試してみたい人は以下をコピペしてテーブルを作ってください。
CREATE TABLE [dbo].[nedan]( [大項目] [nvarchar](50) NULL, [中項目] [nvarchar](50) NULL, [値段] [int] NULL, [備考] [nvarchar](50) NULL ) ON [PRIMARY] GO
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
くだもの リンゴ 100
くだもの みかん 150
くだもの スイカ 3000 ←二番目
くだもの メロン 5000 ←一番高い
さかな さば 100
さかな くえ 10000 ←一番高い
さかな さんま 200 ←二番目
さかな さより 40
おにく ぶた 150
おにく うし 300 ←二番目
おにく うま 500 ←一番高い
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
※TSV形式になってます。
それではテーブルを見てみましょう。
ここにrow_number関数で大項目毎に連番を振ってみます。
文法は以下です。
/****** SSMS の SelectTopNRows コマンドのスクリプト ******/
SELECT TOP (1000) [大項目]
,[中項目]
,[値段]
,row_number() over(partition by [大項目] order by [値段] desc ) as num
,[備考]
FROM [test001].[dbo].[nedan]
備考書いておいてよかったです。
説明が省けました。
各項目ごとに、２番目の値が一目でわかるようになりました。
あとは、この結果に対して
その２）SQLテクニック Selectした結果をSelectする
をするだけです。
クエリにするとこの様な感じです。
select * from
(
SELECT TOP (1000) [大項目]
,[中項目]
,[値段]
,row_number() over(partition by [大項目] order by [値段] desc ) as num
,[備考]
FROM [test001].[dbo].[nedan]
)XX
ここまでは元の結果と同じですね。
架空のテーブルとして XX を定義しています。
このXXに対して、 XX.num=2 とする事で２番目に大きな値を求める事が出来ます。
クエリは此方です。
select * from
(
SELECT TOP (1000) [大項目]
,[中項目]
,[値段]
,row_number() over(partition by [大項目] order by [値段] desc ) as num
,[備考]
FROM [test001].[dbo].[nedan]
)XX
where XX.num='2'
見事に２番目の値だけを求める事ができました！
また、本当はみんなが欲しいであろう 中項目 の値もしっかりわかります。
この方法を利用すれば、
２番目に大きな値や２番目に少ない値なども取得できます。つまり応用の範囲が広いと言う事ですね。※asc descを切り替えることによって
SQL学習サイトオープン
SQLが自習できるサイトをオープンしました！！
そのなも・・・
初心者向けSQL学習サイト
初学者はご活用ください。
( *´艸｀)
ちょっと一息！大人のコーナー
え・・・ 頭がおかしいセール情報を見つけました。97%OFFらしいです。
あだるてぃんな内容なので、そういうのに興味がある人だけ見てください。
※お仕事中なら見ないでください。
※帰りの電車とかでも見ないでください。
