MySQL复杂的自我加入

好吧,只是为了安抚怀疑者.请注意,我并不是认真地提倡这个解决方案,因为它只是不具备可扩展性 - 并且使其可扩展(通过准备好的语句和sproc,坦率地说,单调乏味)......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL PRIMARY KEY
,name VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1  ,'test'),
(2  ,'test1'),
(3  ,'test'),
(4  ,'test1'),
(5  ,'test2');


SELECT MAX(CASE WHEN name = 'test' THEN id END) 'test' 
     , MAX(CASE WHEN name = 'test1' THEN id END) 'test1' 
     , MAX(CASE WHEN name = 'test2' THEN id END) 'test2' 
  FROM 
     ( SELECT x.*
            , COUNT(*) rank 
         FROM my_table x 
         JOIN my_table y 
           ON y.name = x.name 
          AND y.id <= x.id 
        GROUP 
           BY id
     ) z GROUP BY rank;

+------+-------+-------+
| test | test1 | test2 |
+------+-------+-------+
|    1 |     2 |     5 |
|    3 |     4 |  NULL |
+------+-------+-------+