mysql5の質問です


以下のようなテーブルがあります。
テーブル名:access
フィールド名:id,user_id,datetime

ユーザーがサイトにアクセスした時間を記録するテーブルです。
datetimeはタイムスタンプ形式です。


やりたいこと:ユーザー最後にアクセスした時間が新しい順に並べたい。
その際に重複するuser_idは消したい(新しいものだけ残すということです)


とりあえず自分が書いたSQLはこちら
SELECT DISTINCT user_id FROM access ORDER BY datetime

distinctを使うとdistinctの直後に書いたフィールド意外は取得できないのでしょうか?どうも正常に動きません。


予算200ポイント程でお願い致します。

回答の条件
  • 1人5回まで
  • 登録:2007/11/05 20:27:59
  • 終了:2007/11/12 10:39:37

回答(5件)

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912007/11/05 21:25:58

ポイント25pt

下記のようでどうでしょうか。

SELECT user_id, max(datetime) FROM access GROUP BY user_id ORDER BY max(datetime) DESC

user_id ごとにまとめて、その中で最大な日付が、直近のアクセス履歴になると思います。

それを降順で表示すれば、ご希望の結果になるのではないでしょうか。

http://www.atmarkit.co.jp/fnetwork/rensai/sql03/sql1.html

id:tokyosmash

早速のご回答ありがとうございます。

実際に試してみましたが、確かにuser_idは直近のアクセス通りに並びました。

ただ、idとdatetimeが最新のものではなく最も古いレコードのものになってしまっています。

これはどうすればいいのでしょうか・・。

2007/11/05 22:12:32
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402007/11/05 22:39:43

ポイント50pt

ただ、idとdatetimeが最新のものではなく最も古いレコードのものになってしまっています。

GROUP BY を使用した場合は指定したカラムと集合関数しか使えません。

id も表示したければ、

SELECT a.* FROM access AS a WHERE datetime = ( SELECT max(datetime) FROM access AS b WHERE a.user_id = b.user_id ) ORDER BY datetime DESC

などとするしかないでしょう。


ただ、datetime が古いレコードというのは解せませんね。

max という言葉どおり同じ user_id の中で最大(最新)のものを表示しているはずです。

datetimeはタイムスタンプ形式です。

とありますが、実際には型は何でしょう?

文字通り timestamp 型であれば自動更新の副作用があるので、思った値と別の値が入っている可能性もありますが。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.2.2.2 DATETIME、DATE、TIMESTAMP 型

id:Mook No.3

Mook回答回数1312ベストアンサー獲得回数3912007/11/05 22:56:18

ポイント25pt

id も表示したかったのですね。


b-wind さんがもっとエレガントな解を提示していそうな気もしますが、下記のようでどうでしょうか。

SELECT * FROM access WHERE CONCAT(user_id,datetime) IN
(SELECT CONCAT(user_id,max(datetime)) FROM access GROUP BY user_id)
ORDER BY datetime DESC
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/05 23:26:32

ポイント25pt

<SQL例1>

select *
 from `access`
 where (user_id,`datetime`) in(select user_id,max(`datetime`)
                                      from `access`
                                      group by user_id)
 order by user_id 

<SQL例2>

select *
 from `access` as x
 where `datetime`=(select max(`datetime`)
                         from `access`
                         where user_id=x.user_id)
 order by user_id
id:chuken_kenkou No.5

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/05 23:46:57

ポイント25pt

id列は、時系列に+1ずつ増加するのでしょうか?

同じuser_idで、まったく同じタイムスタンプということもあり得るでしょうか?

その場合の対応版です。

select * from `access`
 where id in(select max(id)
              from `access` as x
              where `datetime` in(select max(`datetime`)
                                   from `access`
                                   where user_id=x.user_id)
              group by user_id
            )
 order by id
  • id:tokyosmash
    ただuser_idの並びだけ取得すればいいのではなく、他のフィールドの値もきちんと取得したいです。何かいい方法は無いでしょうか。
  • id:chuken_kenkou
    b-windさんが触れている

    「GROUP BY を使用した場合は指定したカラムと集合関数しか使えません。」

    に関し、MySQLは仕様拡張しているので注意してください。

    http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html

    select id,user_id,max(`datetime`)
    from `access`
    group by user_id

    というSQLは、標準SQLでは指定できないことになっているし、殆どのRDBMSでは文法エラーに
    なります。
    これは、group byで指定していないid列を、指定しているからです。
    ところが、MySQLでは、上記の構文がエラーにはならず、一見、正常に動いたように動作します。
    しかし、idの値は、最大値になるとか、最小値になるとかは、一切、保証されていません。

    MySQLでは、このケースでは、user_idでグループ化することで、idの値も一意になるなら、使用して
    良いとしています。
  • id:tokyosmash
    みなさん回答ありがとうございます。


    急いでいたもので少し大雑把な質問の仕方をしてしまいました。
    反省しております。

    こちらに関しては少し時間をかけてコメントさせてもらいます。
    よろしくお願い致します。
  • id:b-wind
    >「GROUP BY を使用した場合は指定したカラムと集合関数しか使えません。」
    > に関し、MySQLは仕様拡張しているので注意してください。

    > user_idでグループ化することで、idの値も一意になるなら
    という条件が成り立たない場合値が不確定になるので注意が必要です。
    うまく条件が合えば便利な機能ではありますが。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません