MySQL复杂的自连接 [英] MySQL complex self join

问题描述

我有一张这样的桌子:

id | name
--------
1  | test
2  | test1
3  | test
4  | test1
5  | test2

我正在尝试编写一个查询，它会给出这样的结果

I am trying to write a query that will give a result like this

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

很明显只有三个名字，我正在尝试编写查询，但我不能.有人可以帮我吗

And it is very clear there will be only three name, I am trying to write the query, But I couldn't. Can someone help me

推荐答案

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

Well, just to appease the doubters. Note, I'm not seriously advocating this as a solution, because it's just not very scalable - and making it scalable (via prepared statements and a sproc is, frankly, tedious)...

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 |
+------+-------+-------+

这篇关于MySQL复杂的自连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！