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

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

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

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

1106閲覧

1対多の関係にあるテーブル間で複数の検索条件に一致して重複してしまうレコードの抽出

annderber

総合スコア98

MySQL

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2018/11/12 09:29

編集2018/11/12 10:19

お世話になります。

イメージ説明

documentテーブルがメインの文章テーブル、view_continueはブックマークテーブルのようなイメージです。
登録した文章はユーザーごとにお気に入り設定が出来るようになっていて、view_continueにはuser_idも登録されるようになっています。

〇データサンプル
documentテーブル
|id|title|report|update_date|
|:--|:--:|--:|
|1|test|text|2018-11-12|

view_continueテーブル

iddocument_iduser_id
11001
21002

画面に一覧表示する際にいくつか検索条件があります。
1.documentのレコードで登録された日付が当日中のレコード
or
2.view_continueレコードにログインユーザーのidが存在する場合

この条件でjoinした2つのテーブルで検索するとレコードが重複されるのでgroup byします。

sql

1SELECT `d`.`id`, `d`.`title`, `d`.`report`, `d`.`update_date`, `vc`.`user_id` FROM `document` as `d` left join `view_continue` as `vc` on `d`.`id` = `vc`.`document_id` where `update_date`= '2018-11-12' || `user_id` = '002' GROUP by `d`.`id`;

とした場合にuser_idが002のユーザーがログインして画面を表示させると、
|id|title|report|update_date|user_id|
|:--|:--:|--:|
|1|test|text|2018-11-12|001|

となり、他ユーザーのidが紛れ込んでしまいます。
これを検索したuser_idが入るようになる処理を教えていただきたいです。
documentテーブルのレコードは1万件程度の想定です。
基本的にSQLの構文についての質問ですが、テーブル設計に関してのご指摘でも大丈夫です。

よろしくお願いいたします。

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

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

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

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

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

hihijiji

2018/11/12 09:44

単にWHERE の or を and にするのはダメなのですか? 別件ですが、その構造だとユーザーがtitleに対して複数のブックマークを設定できませんか?
annderber

2018/11/12 09:50 編集

コメントありがとうございます。ブックマークをしていなくても当日登録したレコードは表示させたいのでor条件にしたいです。同一のtitleに対して複数ブックマークが出来てしまうということでしょうか?それであれば問題ないです
hihijiji

2018/11/12 10:10

了解しました。提示のSQLではdocumentのidしか取れませんよ。他ユーザーのidが入るのは別の問題ですのでそこのSQLを提示してください。
annderber

2018/11/12 10:21

申し訳ありませんdocumentのidしかとれないというのはどういうことでしょうか。確かに提示したsqlでは結果のレコード通りにならないので修正いたしました。
hihijiji

2018/11/13 01:12

idしかとれないって表現は適切ではなかったですね。正確にはdocumentのid以外は不定値になるです。GROUP BYで指定したカラムと集計値以外は通常使いません。
annderber

2018/11/13 01:21

申し訳ありませんが、hihijijiさんが求めているsqlがどういうものであるのかが分かりかねます。
hihijiji

2018/11/13 01:33

この場合SELECT 句の`d`.`id`以外は使っちゃだめってことだけです。
guest

回答2

0

要件をまとめると、一覧表示する文章は以下の2つですね?

  • 当日登録された文章
  • ユーザがお気に入りに登録した文章

これらを重複なしで取得したいと。
単純にそれぞれ別にSELECTしてUNIONで繋げではどうでしょうか。

SQL

1SELECT d.*, NULL AS user_id 2FROM document as d 3WHERE d.update_date= '2018-11-12' 4UNION 5SELECT d.*, vc.user_id 6FROM document as d 7 INNER JOIN view_continue AS vc ON d.id = vc.document_id 8WHERE vc.user_id = '002'

投稿2018/11/12 11:41

kgm

総合スコア275

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

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

sazi

2018/11/12 12:22 編集

UNIONマージするより、件数的に少ない方に除外条件付けて、union allにした方が高速な気もする。
annderber

2018/11/13 00:40

コメントありがとうございます。 提示いただいたsqlだと同じレコードが2つ取得できてしまいます。 これは重複なしで取得したいです。
kgm

2018/11/13 05:43

あーそっか。重複しちゃいますね。 saziさんのご指摘のように、件数が少ない方(お気に入り)に当日登録されたレコードを除外すべきでした。 こんなもんかな。 SELECT d.*, NULL AS user_id FROM document as d WHERE d.update_date= '2018-11-12' UNION ALL SELECT d.*, vc.user_id FROM document as d INNER JOIN view_continue AS vc ON d.id = vc.document_id WHERE vc.user_id = '002' AND d.update_date != '2018-11-12'
sazi

2018/11/13 05:53 編集

>KGMさん それでも駄目ですね。 日付指定された方に指定したユーザーのIDがある場合には、NUll固定ではなく表示しないと駄目なので。 ※対応したものは私の回答に追記しています。
kgm

2018/11/13 06:44

当日登録された文章でお気に入りに登録されているものはuser_idを引きたいということですね。 それなら確かにもうちょっと手を加えないといけませんが・・・。 saziさんが提示されたSQLだと、当日登録された文章で指定ユーザがお気に入りに登録していないものまで指定ユーザのuser_idとしちゃってますよね。 論理的に考えて、ちょっと違和感があります。 当日登録&お気に入り非登録のものはuser_id = nullとすべきなんじゃないかと考えました。 まあ、annderberさん次第ですが。 ということで、最終的にはこんなSQLに。 SELECT d.*, NULL AS user_id FROM document as d INNER JOIN view_continue AS vc ON d.id = vc.document_id WHERE vc.user_id != '002' AND d.update_date = '2018-11-12' UNION ALL SELECT d.*, vc.user_id FROM document as d INNER JOIN view_continue AS vc ON d.id = vc.document_id WHERE vc.user_id = '002'
sazi

2018/11/13 07:34 編集

>当日登録された文章で指定ユーザがお気に入りに登録していないものまで指定ユーザのuser_idとしちゃってますよね。 いえ、existsで判定しているので、「含んでいたら」です。 そのSQLだと、結合してるので記事が'002'以外のユーザー分の件数になっちゃいますよ。 それに、inner join してるから、view_continueが無ければ抽出されないし。
kgm

2018/11/13 07:11

要件がはっきりしないので議論してもアレなんですが・・・。 質問文には以下の2条件が抽出対象となっています。 1.documentのレコードで登録された日付が当日中のレコード or 2.view_continueレコードにログインユーザーのidが存在する場合 つまり、当日登録分のdocumentに関してはログインユーザーがview_continueにある無しに関わらず(お気に入り登録の有無に関わらず)、すべて取得するのでは?
annderber

2018/11/13 07:18 編集

kgmさんのsqlはちゃんと確認できていませんが、 要件としては当日登録したdocumentのレコードはview_continueの有無に関わらず表示させます。 view_continueは古い(当日以前)documentレコードを継続して表示させるために使用します。
kgm

2018/11/14 14:07

でしたら、やはりsaziさんの追記2以外のSQLでは、view_continueにない当日分が取得できていないとこうことですね。 追記2はview_continueにない当日分も取得できるのですが、ログインしたユーザのIDが入ってしまうと。 ああ、やっと結論にたどり着けたw
guest

0

ベストアンサー

group by じゃなくて、単にDISTINCTすればいいような。

SQL

1SELECT distinct d.id, d.title, d.report, d.update_date, vc.user_id 2FROM document as d inner join view_continue as vc on d.id=vc.document_id 3where d.update_date= '2018-11-12' 4 and vc.user_id='002'

ユーザーは限定されるので、特に結合しなくても固定で良いと思います。
こちらの方が高速かもしれません。
※[]はパラーメータ

SQL

1SELECT id, title, report, update_date, [ユーザーid] as user_id 2FROM document d 3where update_date=[日付] 4 and exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)

追記

ログインしたユーザーでなければ、user_idを取得しないという解釈で。

SQL

1SELECT id, title, report, update_date 2, case when exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id) 3 then [ユーザーid] end as user_id 4FROM document d 5where update_date=[日付] 6 or exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)

または、(見た目はすっきりするけど、前のよりは遅いと思われる)

SQL

1SELECT *,case when user_exists then [ユーザーid] end as user_id 2FROM ( 3 SELECT id, title, report, update_date 4 ,exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id) as user_exists 5 from document 6) as d 7where update_date=[日付] 8 or user_exists

追記2

UNION クエリー版

SQL

1SELECT d.* 2 , case when exists(select 1 from view_continue where user_id='002' and id=d.id) 3 then '002' end as user_id 4FROM document as d 5WHERE d.update_date='2018-11-12' 6UNION ALL 7SELECT d.*, vc.user_id 8FROM document as d 9 INNER JOIN view_continue AS vc ON d.id = vc.document_id 10WHERE d.update_date<>'2018-11-12' and vc.user_id = '002'

投稿2018/11/12 10:40

編集2018/11/13 02:54
sazi

総合スコア25083

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

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

kgm

2018/11/12 11:18

それだと特定のユーザがお気に入りにした文章しか取得できないのでは? 本日登録された文章とユーザがお気に入りに登録した文章の2種類を取得したいという要件だと思うのですが。
sazi

2018/11/12 11:38

確かにor条件になってますね。 だとすると、当日分で他の人がお気に入りしたもののユーザidをどうするのかというのがはっきりしないと、要件が矛盾しますね。
annderber

2018/11/13 00:43 編集

コメントありがとうございます。 参考にさせていただきます。 思ったよりも複雑になってしまうのですね。もっと簡便なsqlでかけるものかと考えていました。 普段簡単なsqlしか書かないので勉強になりました。
sazi

2018/11/13 00:54

性能を意識したので、記述は冗長だったりします。 シンプルなだけの記述を追記してみましたので、確認してみて下さい。
annderber

2018/11/13 01:01

色々なパターンありがとうございます。 確かにレコード数はそこそこある想定だったので性能は意識したいところでした。 まずシンプル版でパフォーマンスを見つつ、場合によって性能意識版も試したい思います。 ありがとうございました。
annderber

2018/11/13 01:09

すいません。 シンプル版試したところ同一レコードが2つ取得されてしまいました。 重複なく取得したのですが、方法ありますでしょうか。
sazi

2018/11/13 02:06 編集

修正しました。
annderber

2018/11/13 02:24 編集

修正ありがとうございます。 とりあえず追記1の方で試させていただいてうまくいきました。 ありがとうございます。 exists周りのsqlの働きがよく分かっていないので調べたいと思います。
sazi

2018/11/13 02:56 編集

追記2も駄目ですね。検証できていなくて手間取らせてしまいましたね。すみません。 一応修正しておきました。 面倒なのは指定した日付の記事に複数のユーザーがいいねをしている時に、ログインユーザが含まれている場合のみそのユーザー名を表示する仕様です。 結局指定した日付の件数を記事の件数に合わせるには、selectでのサブクエリーしかなさそうですね。
annderber

2018/11/13 04:31

テーブル設計を変えるのもありでしょうか。 documentテーブルにカラムを一つ追加して、お気に入りしたユーザー名をカンマ区切りで入れるとか。 アンチパターンになりますが。
sazi

2018/11/13 04:39

テーブルの設計は正規化されたものだと思います。 ユーザー名をカンマ区切りにして保持すると、インデックスは当てに出来なくなります カンマ区切りというのはgroup_concat()を使用すれば可能ですので、SQLの表現としては分かりやすくなるかもしれませんが、性能には期待はできなくなります。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.51%

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

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

質問する

関連した質問