mysql - MySQL-使列成行

我有一张这样的直径表：

TreeID  Diameter
----------------
1       3
1       2
1       3
2       2
2       2
2       1
1       3

我希望每棵树的直径按以下降序排列为6倍：

TreeID  Diameter 1  Diameter 2  Diameter 3  Diameter 4  Diameter 5  Diameter 6
------------------------------------------------------------------------------
1       3           3           3           2
2       2           2           1

我要查询直径1到6：

(SELECT Diameter FROM TreeDiameters ORDER BY Diameter DESC LIMIT 1 OFFSET 1) AS Diameter1

但我需要放一个WHERE子句，以便此子选择中的TreeID与主选择中的TreeID匹配。什么是WHERE子句？

最佳答案

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(measurement_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,TreeID  INT NOT NULL
,Diameter INT NOT NULL
);

INSERT INTO my_table (treeid,diameter) VALUES
(1      , 3),
(1      , 2),
(1      , 3),
(2      , 2),
(2      , 2),
(2      , 1),
(1      , 3);

SELECT x.*
     , COUNT(*) rank
  FROM my_table x
  JOIN my_table y
    ON y.treeid = x.treeid
   AND
     ( y.diameter > x.diameter
       OR (y.diameter = x.diameter AND y.measurement_id <= x.measurement_id )
     )
  GROUP
     BY x.treeid, x.diameter, x.measurement_id
-- HAVING rank < something
  ORDER BY treeid,rank;

  +----------------+--------+----------+------+
  | measurement_id | TreeID | Diameter | rank |
  +----------------+--------+----------+------+
  |              1 |      1 |        3 |    1 |
  |              3 |      1 |        3 |    2 |
  |              7 |      1 |        3 |    3 |
  |              2 |      1 |        2 |    4 |
  |              4 |      2 |        2 |    1 |
  |              5 |      2 |        2 |    2 |
  |              6 |      2 |        1 |    3 |
  +----------------+--------+----------+------+

关于mysql - MySQL-使列成行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25495046/