sql - GROUP BY を使用した後の MAX 値を持つ行の検索

dept_no、emp_no、from_date、to_date の 4 つの列を持つテーブルがあります。各 dept_no、emp_no はマネージャーです。

to_date を使用して現在のマネージャーを検索し、dept_no と emp_no を表示したいと考えています。

サンプルデータ:

|emp_no  |dept_no  | from_date  |   to_date  |
| 11     | d001    | 1985-01-01 | 1991-10-01 |
| 12     | d001    | 1991-10-01 | 9999-01-01 |
| 21     | d002    | 1985-01-01 | 1989-12-17 |
| 22     | d002    | 1989-12-17 | 9999-01-01 |
| 31     | d003    | 1985-01-01 | 1992-03-21 |
| 32     | d003    | 1992-03-21 | 9999-01-01 |

サンプル出力:

|emp_no   |dept_no  |
|12       |d001     |
|22       |d002     |
|32       |d003     |

私はこれを理解しました:

SELECT dept_no
     , emp_no 
  FROM 
     ( SELECT dept_no
            , MAX(to_date) as cur 
         FROM dept_manager 
        GROUP 
           BY dept_no) as new 
  JOIN dept_manager using(dept_no) 
 where cur = to_date;

各部門の MAX(to_date) を見つけて、それを WHERE 句で使用しています。

これは機能しますが、もっと良い方法があるはずだと思います。

同様の質問をたくさん見ましたが、グループ化で使用できない列を表示したいので、どれも役に立ちませんでした。

サンプル データと予想される結果を共有してください。 row_number を使用できます。

2020 年 9 月 3 日 19:45

meta.stackoverflow.com/questions/333952/… を参照してください。

2020 年 9 月 3 日 19:47

MySQL <> SQLサーバー。 1 つのデータベースのみをタグ付けしてください。

2020 年 9 月 3 日 19:52

移植可能で通常は効率的なアプローチは、サブクエリを使用してフィルタリングすることです。

select dept_no, emp_no
from dept_manager d
where to_date = (select max(d1.to_date) from dept_manager d1 where d1.dept_no = d.dept_no)

このクエリのパフォーマンスを向上させるには、(dept_no, to_date) のインデックスが必要です。

もう 1 つの一般的なアプローチはウィンドウ関数です。

select *
from (
    select d.*, row_number() over(partition by dept_no order by to_date desc) rn
    from dept_manager d
) d
where rn = 1

データベースとバージョンによっては、より適切な代替手段がある可能性があります。

1

1

サブクエリによるフィルタリングは機能し、私がやりたいことにとって最良のアプローチのように思えます。ありがとうございます。

2020 年 9 月 3 日 20:18