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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

2回答

1377閲覧

1対多のテーブルからの最大値によるデータ取得について

strawberry

総合スコア14

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2019/02/14 11:49

編集2019/02/14 13:09

表題の件に関して、ご教示いただけますと幸いです。
実際のテーブルの簡略版のため、テーブル構造に関するご指摘は御容赦いただけますと幸いです。

userテーブルのデータ1に対して、actionテーブルのデータは多数あります。
userテーブル:actionテーブルが1:0になることもありえます。
user毎にactionテーブルのdateが最大または、actionテーブルにデータが最大のレコードを取得しようと色々試したのですが、
いまいちうまくいきません。

サンプルデータ(actionテーブルのレコードが1-3まで) の場合、理想としている結果は、下記のとおりです。

###質問1
user_idをキーとしてactionテーブルとuserテーブルを結合し、userに対してactionテーブルのレコードが複数ある場合には、action.dateが最も最新のデータのみを取得したいです。
また、合わせて、actionにレコードが存在しないuserは、actionのカラムをNULLにした形で取得したいです。
下記のような結果を取得するには、どのようなSQLが適当でしょうか?

user_id,name,action_id,date,action =====================================  1,yamada,2,2019-01-02,rest  2,suzuki,NULL,NULL,NULL  3,sato,3,2019-01-02,shopping

###質問2
サンプルデータで、actionテーブルのレコードが1-4まであった場合、
yamadaさんのレコードをactionがworkのレコードで絞り込みたいです。
その際にはどのようなSQLが適当になりますでしょうか。

DBはMySQL5.6系になります。
お手数ですが、ご教授いただけますと幸いです。

★サンプルデータ★

※userテーブルのuser_idが、actionテーブルのuser_idとマッピングされます。

######【userテーブル】

  • テーブル作成

SQL

1CREATE TABLE `user` ( 2`user_id` bigint(20) NOT NULL AUTO_INCREMENT, 3`name` text, 4PRIMARY KEY (`user_id`) 5);
  • データ投入

SQL

1INSERT INTO user VALUES(NULL,"yamada"); 2INSERT INTO user VALUES(NULL,"suzuki"); 3INSERT INTO user VALUES(NULL,"sato");
  • テーブル内のデータ
user_idname
1yamada
2suzuki
3sato

######【actionテーブル】

  • テーブル作成

SQL

1CREATE TABLE `action` ( 2`action_id` bigint(20) NOT NULL AUTO_INCREMENT, 3`user_id` bigint(20) NOT NULL, 4`date` date NOT NULL, 5`action` text NOT NULL, 6PRIMARY KEY (`action_id`) 7);
  • データ投入

SQL

1INSERT INTO action VALUES(NULL,1,"2019-01-01","work"); 2INSERT INTO action VALUES(NULL,1,"2019-01-02","rest"); 3INSERT INTO action VALUES(NULL,3,"2019-01-02","shopping"); 4INSERT INTO action VALUES(NULL,1,"2019-01-01","shopping");
  • テーブル内のデータ
action_iduser_iddateaction
112019-01-01work
212019-01-02rest
332019-01-02shopping
412019-01-01shopping

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2019/02/14 11:50

create文とサンプルデータ投入用のinsert文をご提示ください。
strawberry

2019/02/14 12:15

閲覧いただきありがとうございます。 両方追記いたしました。 よろしくお願いいたします。
退会済みユーザー

退会済みユーザー

2019/02/14 12:20

追記は確かにされているようですが、「やっつけ仕事だな」と言う印象が拭えないのですが。
strawberry

2019/02/14 12:36

大変失礼致しました。 各サンプルデータに関して、見やすいようにフォーマットいたしました。
退会済みユーザー

退会済みユーザー

2019/02/14 12:40

1対多のデータをのぞみの形で取るための条件は何?
guest

回答2

0

ベストアンサー

要件が曖昧なので、条件は推測。
求める結果は一致した。

sql

1select * from user U 2left join action A on U.user_id = A.user_id 3where (U.user_id, A.date) IN ( 4 select user_id, max(date) from `action` 5 group by user_id 6) 7or A.date is null 8order by U.user_id 9

投稿2019/02/14 13:05

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

strawberry

2019/02/15 02:21

ご回答いただきありがとうございます。 頂いた方法で解決することができました。
guest

0

SQL

1select usr.user_id, usr.name, act.action_id, act.date, act.action 2from user usr left join ( 3 select * from action a 4 where (date, action_id) in ( 5 select max(date), max(action_id) from action 6 where user_id=a.user_id and date>=a.date 7 ) 8 ) act on usr.user_id=act.user_id 9order by usr.user_id

または

SQL

1select usr.user_id, usr.name, act.action_id, act.date, act.action 2from user usr left join action act 3 on usr.user_id=act.user_id 4where (act.date, act.action_id) in ( 5 select max(date), max(action_id) from action 6 where user_id=usr.user_id and date>=act.date 7 ) 8 or act.action_id is null 9order by usr.user_id

投稿2019/02/14 13:14

編集2019/02/14 13:18
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

strawberry

2019/02/15 02:23

2パターンご回答いただきありがとうございます!
sazi

2019/02/15 02:32

dateが最大の日付が複数ある場合のaction_idも限定するようにしています。 不要な要件であれば、Kosuke_Shibuyaさんの回答で大丈夫です。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問