一人アドカレ
| 日 | ポスト |
|---|---|
| 1 | 一時テーブルが存在していたら削除する |
| 2 | 何度も使う中間テーブルに名前をつけてクエリを見やすくする |
| 3 | 共通テーブル式と一時テーブルの参照可能な範囲の違い |
| 4 | グループ毎に最大値や最小値を持つレコードを抽出する |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 15 | |
| 16 | |
| 17 | |
| 18 | |
| 19 | |
| 20 | |
| 21 | |
| 22 | |
| 23 | |
| 24 | |
| 25 |
グループ毎に最大値や最小値を持つレコードを抽出する
この記事は自身のブログを書き直したものです。
課題
グループ毎に最大値や最小値を持つレコードを抽出する。
最大値や最小値を得るのではなく、最大値や最小値を持つレコードを抽出したい。つまり、集計前のレコードがほしい。
準備
実行環境
- SQL Server 2017 Standard
- SQL Server 2008 R2 Express, 2012 Standardでも確認済
- SQL Server Management Studio 2017
サンプルデータ
fruitsテーブル
| id | name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | apple | 120 |
| 3 | orange | 50 |
| 4 | orange | 40 |
| 5 | apple | 80 |
| 6 | orange | 60 |
| 7 | banana | 120 |
| 8 | banana | 150 |
解法
fruitsテーブルから、[name]ごとに最低の[price]をもつレコードを抽出する。
ROW_NUMBERを使った例
SELECT [id]
, [name]
, [price]
FROM ( SELECT *
, ROW_NUMBER() OVER ( PARTITION BY [name]
ORDER BY [price] ) AS [seq]
FROM [fruits] ) AS [fr]
WHERE [seq] = 1
ORDER BY [id];
not existsを使った例
SELECT [id]
, [name]
, [price]
FROM [fruits] AS [base]
WHERE not exists ( SELECT *
FROM [fruits]
WHERE [fruits].[name] = [base].[name]
AND [fruits].[price] < [base].[price] )
ORDER BY [id];
JOINを使った例
SELECT [main].[id]
, [main].[name]
, [main].[price]
FROM [fruits] AS [main]
INNER JOIN ( SELECT [name]
, MIN( [price] ) AS [Price]
FROM [fruits]
GROUP BY [name] ) AS [sub]
ON ( [sub].[name] = [main].[name]
AND [sub].[price] = [main].[price] )
ORDER BY [main].[ID];
感想
同じ目的でも複数の方法で実現できることもある。
実行プランを確認し、使い分けを考えたい。