一人アドカレ

日	ポスト
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];

感想

同じ目的でも複数の方法で実現できることもある。
実行プランを確認し、使い分けを考えたい。

参考

SQL緊急救命室第1回　サブクエリ・パラノイア～副問い合わせ乱用による性能劣化を治療せよ！