人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

MySQLのJOINを使って結合する方法について質問します。

SELECT * FROM member LEFT JOIN bbs ON member.member_id=bbs.member_id

とした場合、memberテーブルのmember_idとbbsテーブルのmember_idが一致すれば表示されます。

次に「GROUP BY member.member_id」とした場合、bbsに同じmember_idが複数あっても1レコードしか表示されなくなります。(つまり、bbs内の重複したmember_idは表示されない)

そこで質問ですが、結合するbbsテーブルの登録状況が以下の通りだとします。
[bbs_id][member_id][date]
[1][1][2007-10-18]
[2][1][2007-10-19]

上記のSQLで結合すると、bbs_idが1のデータが表示されると思うのですが、dateの新しいbbs_idが2の方を表示させるにはどうしたらいいのでしょうか?

”テーブル同士を結合して、かつグループ化して結合先のレコードは1レコードしか表示しない。
しかしその表示するのは、特定の条件に一致したもの(ORDER BY bbs.date DESC など)”

という事をする為のSQL文の組み立て方を教えていただければと思います。
(MySQLは4.0x系でお願いします。)


●質問者: kt26
●カテゴリ:ウェブ制作
✍キーワード:2007-10-18 BBS GROUP MySQL ON
○ 状態 :終了
└ 回答数 : 5/5件

▽最新の回答へ

1 ● きゃづみぃ
●10ポイント

date が新しいということは 一番最大ということでしょう。

なので 抽出するときに MAX(date) としたらいいですね。

で、ほか出力する内容で GROUP BY してあげたらいいです。

◎質問者からの返答

ありがとうございます。ただ実際には複数テーブルをLEFT JOINで結合しているのですが、

そうするとなぜかMIN/MAXで抽出されず、登録IDが古い方のデータが表示します。

(上の例ならbbs_idが1のデータが表示される)


質問の例だけではなく、複数テーブルを結合した場合でも

目的の結果が得られる方法(または得られない場合の注意点)なども

教えていただければと思います。


2 ● KUROX
●10ポイント

>memberテーブルのmember_idとbbsテーブルのmember_idが一致すれば表示されます。

分かっておられるとは思いますが、念のため。

LEFT JOIN

左側に指定された表のすべての行が表示されます。

------------

このJOINの仕方では、dateがNULLの場合もありえるので、

MAXとか使っても期待通りの結果があれらない場合があります。


3 ● chuken_kenkou
●10ポイント

MySQLは、GROUP BYに関して、拡張仕様があるので注意が必要です。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.7.3 非表示のフィールドに対する GROUP BY

標準SQLや主要なRDBMSでは、以下のような記述はエラーになります。

select c1,c2,c3
 from t1
 group by c1

しかし、MySQLでは、エラーにしていません。C1をグループ化することで、C2、C3の値も一意になるなら、

この構文を使用してよいことになっています。

一方、C1をグループ化しても、C2、C3の値が一意にならないなら、結果を保証しないと明記されています。

◎質問者からの返答

なるほど・・。この例で言うとC2は一意にならないので、私が求めている結果にはなりませんね。

ただ、求めている結果で出力されて欲しいので、どうしたらよいのか悩むところです。


4 ● chuken_kenkou
●100ポイント

特定のmember_idについて、結果を得るなら、「LIMIT n」を使う方法はいかがでしょうか?

SELECT *
 FROM `member`
 LEFT JOIN bbs
 ON member.member_id=bbs.member_id
 WHERE member.member_id=1
 ORDER BY bbs.`date` DESC
 LIMIT 1;

複数のmember_idについて、結果を得たい場合は、MySQL 4.0系だと、一時表(テンポラリ・テーブル)を

利用するといった方法になります。


-- 一時表の定義
CREATE TEMPORARY TABLE wbbs
SELECT member_id,MAX(`date`) as `date`
 FROM bbs
 GROUP BY member_id,bbs_id;

-- 検索
SELECT *
 FROM `member`
 LEFT JOIN wbbs
 ON `member`.member_id=wbbs.member_id
 LEFT JOIN bbs
 ON wbbs.member_id=bbs.member_id
 and wbbs.`date`=bbs.`date`
◎質問者からの返答

テンポラリテーブルを使って上手くできました!!

こんな便利な機能があったんですね。だいぶ使い方の幅が広がったと思います。

ソースもいただき、ありがとうございました。


5 ● 敷守ほむら
●10ポイント

SQL Serverで確認したので、MySQLではできないかもしれませんが…

select * 
from member C 
left join (
select A.bbs_id, A.member_id, A.[date] 
from bbs A 
inner join (select max(bbs_id) as bbs_id, member_id from bbs group by member_id) B 
on B.bbs_id = A.bbs_id
) D 
on C.member_id = D.member_id
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ