MySQLのテーブル自己結合は微妙…

SQL

プログラムで疲れた頭を解すにはSQLに限る。以下みたいなテーブルで

id user_id value ctime
1 1 hoge 2008-01-01
2 3 fuga 2008-01-17
3 3 moge 2008-02-02
4 2 piyo 2008-01-11
5 1 puni 2008-02-01

各ユーザが最後に追加したレコードをSQL一発でまとめて取得するにはどうすればいいだろうか？テンポラリテーブルを作るか。いいや、インラインビューを使うね。

SELECT t1.id, t1.user_id, t1.value, t1.ctime 
FROM hoge_table t1
INNER JOIN (
    SELECT user_id, MAX(ctime) FROM hoge_table GROUP BY user_id
) AS t2 
ON t1.ctime = t2.ctime 
AND t1.user_id = t2.user_id;

同一のテーブルのデータを参照するSQLはかなり強力。たとえば、重複レコードの削除は自己相関サブクエリを使うと簡単にできる。

DELETE FROM hoge_table t1 
WHERE EXISTS (
    SELECT 1 
    FROM hoge_table t2 
    WHERE t1.user_id = t2.user_id 
    AND t1.value = t2.value 
    AND t1.id < t2.id
);

しかし、MySQL 5.0では自己相関サブクエリのような変更するテーブルと同じテーブルをサブクエリが参照するinsert delete updateはエラーになるのだった。MySQLは単純なSELECTに関してひたすら強力だけど、それ以外はビミョーだ。

今度こそテンポラリテーブルが必要なのか、面倒くさい、ていうか何かに負けた気がする。誰かもっと楽な方法知らん？

CREATE TEMPORARY TABLE fuga_table ( id BIGINT );

INSERT INTO fuga_table (id) (
  SELECT t1.id 
  FROM hoge_table t1 
  WHERE EXISTS (
    SELECT 1 
    FROM hoge_table t2 
    WHERE t1.user_id = t2.user_id 
    AND t1.value = t2.value 
    AND t1.id < t2.id
  )
);

DELETE FROM hoge_table t1 WHERE EXISTS (SELECT 1 FROM fuga_table t2 WHERE t1.id = t2.id);

まだエラーが出る。

DELETE FROM hoge_table WHERE EXISTS (SELECT 1 FROM fuga_table t2 WHERE hoge_table.id = t2.id);

delete のときはテーブル名エイリアスがつかえないらしい…

追記

以下の書き方でテンポラリテーブルを使わずに重複レコードを削除できるらしい。

DELETE h1 FROM  hoge_table h1, hoge_table h2 WHERE  h2.user_id = h1.user_id AND h2.value = h1.value AND h1.ctime < h2.ctime ;