質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.80%

重複行を排除したビューの作成について

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 2,775

score 33

MySQLバージョン:5.6

テーブル定義
CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) NOT NULL COMMENT '利用者',
  input_date date NOT NULL COMMENT '入力日',
  name_sei varchar(32) NOT NULL COMMENT '名前(姓)',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

データ定義
INSERT INTO test VALUES (1,63,'2015-07-01','相沢'),(2,63,'2015-08-11','相沢'),(3,63,'2015-08-10','相沢'),(4,64,'2015-07-04','大畑'),(5,64,'2015-08-09','大畑'),(6,65,'2015-08-10','菅野'),(7,63,'2015-07-15','相沢');

SELECT結果
+----+---------+------------+----------+
| id | user_id | input_date | name_sei |
+----+---------+------------+----------+
|  1 |      63 | 2015-07-01 | 相沢     |
|  2 |      63 | 2015-08-11 | 相沢     | *
|  3 |      63 | 2015-08-10 | 相沢     |
|  4 |      64 | 2015-07-04 | 大畑     |
|  5 |      64 | 2015-08-09 | 大畑     | *
|  6 |      65 | 2015-08-10 | 菅野     | *
|  7 |      63 | 2015-07-15 | 相沢     |
+----+---------+------------+----------+

上記SELECT結果の*の行、user_idの重複を排除した上でinput_dateの最新を取得する
ビューを作成する必要があり、試行錯誤しております。
以下のクエリでビューを作ってみましたが、idが正常に取得できませんでした。

CREATE VIEW `view_test` AS
    select 
        `test`.`id` AS `id`,
        `test`.`user_id` AS `user_id`,
        max(`test`.`input_date`) AS `input_date`,
        `test`.`name_sei` AS `name_sei`
    from
        `test`
    group by `test`.`user_id`

SELECT結果
+----+---------+------------+----------+
| id | user_id | input_date | name_sei |
+----+---------+------------+----------+
|  1 |      63 | 2015-08-11 | 相沢     |
|  4 |      64 | 2015-08-09 | 大畑     |
|  6 |      65 | 2015-08-10 | 菅野     |
+----+---------+------------+----------+

*の行を取得できるビューを作成する方法をご教示頂けないでしょうか?
宜しくお願い致します。
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

0

他にも定番の方法としては次のような相関サブクエリを使う方法があげられます。
この方法なら user_id と input_date で重複があっても user_id ごとに1行しか返りません。
ただしたいていの場合、こっちのほうが遅いです。

CREATE VIEW `view_test` AS
select * from test t
where t.id = (
  select x.id from test x
  where x.user_id = t.user_id
  order by x.input_date desc, x.id desc
  limit 1
)


また、kokardy さんの回答と考え方はほとんど同じですが、
サブクエリを使わずに group by と having で済ませる方法もあります。
ただし、この書き方は MySQL でしかできません。
(ほか RDBMS だと group by で select にアスタリスクは使えないし group by に test の列を羅列しないといけない)

CREATE VIEW `view_test` AS
select t.* from test t
inner join test x on x.user_id = t.user_id
group by t.id
having t.input_date = max(x.input_date)

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/08/12 11:39

    ご回答ありがとうございました。ビューを作成し、正常に値が取得できました。
    ただお恥ずかしい話ですがご提示頂いたクエリが理解できない部分が多くあります。もしよろしければ、この2つのクエリを導き出した過程をご説明頂けないでしょうか?

    キャンセル

  • 2015/08/14 14:31

    グループごとのトップ1の行を取得する定番のパターンなので、、、導き出したというより、知っていたという感じです。

    キャンセル

  • 2015/08/25 01:45

    申し訳ありません、メール通知が来なかったためお返事に気付きませんでした。これらのクエリは定番パターンなのですね。再度のお返事ありがとうございました。

    キャンセル

0

MySQLの方言的に通るかわかりませんが、
SELECT t.*
FROM
(SELECT "user_id", 
 MAX("input_date") AS max_input_date
 FROM "test" GROUP BY "user_id") AS g
LEFT JOIN "test" AS t
ON t."user_id" = g."user_id" AND t."input_date" = g."max_input_date"
こういうのはいかがですか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/08/12 07:59

    MySQL 的には識別子のクオートはダブルクオートではなくバッククオートなのでそこだけ注意が必要です

    キャンセル

  • 2015/08/12 11:35

    クエリ自体は問題なく正常に値を取得できましたが、ビュー作成時に以下のエラーとなってしまいました。

    ビュー作成クエリ
    CREATE OR REPLACE VIEW `test_view` AS
    SELECT t.*
    FROM
    (SELECT user_id,
    MAX(input_date) AS max_input_date
    FROM test GROUP BY user_id) AS g
    LEFT JOIN test AS t
    ON t.user_id = g.user_id AND t.input_date = g.max_input_date

    エラー内容
    ERROR 1349: View's SELECT contains a subquery in the FROM clause

    MySQLではビュー作成時はFROM句にサブクエリを使用できないようです。
    ですがご提示頂いたクエリは大変勉強になりました、ありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.80%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る