mysql - MySQL:列印每列前3个值的资料列名称

+-----------------------------+------+------+------+
| State                       | 2006 | 2007 | 2008 |
+-----------------------------+------+------+------+
| Andaman and Nicobar Islands |   32 |   27 |   23 |
| Andhra Pradesh              | 3824 | 2432 | 1591 |
| Arunachal Pradesh           |   12 |    9 |   25 |
| Assam                       |  617 |  319 |  530 |
| Bihar                       | 1665 | 1949 | 1944 |
| Chandigarh                  |    0 |    5 |    4 |
| Chhattisgarh                |  374 |  401 |  855 |
| Dadra and Nagar Haveli      |    0 |    0 |    0 |
| Daman and Diu               |    2 |    0 |    1 |
| Delhi                       |    0 |    0 |    0 |
| Goa                         |   72 |    1 |   42 |
| Gujarat                     | 2038 |  328 |  540 |
| Haryana                     |  350 |  520 |  427 |
| Himachal Pradesh            |  323 |  214 |   34 |

我有类似的表，只是有更多的行数。列描述了给定年份的事故数量。我每年需要打印出事故次数最多的前3个州。有一种方法可以一次性完成吗？
目前，我只能按年度进行：

SELECT State AS 'Accidents-2006'
FROM accidents
ORDER BY `2006`
DESC LIMIT 3;

然后在2007年和2008年重复此操作。

编辑：我正在搜索这样的输出：

+-----------------+-----------------+--------------------+
| 2006            | 2007            | 2008               |
+-----------------+-----------------+--------------------+
| Andhra Pradesh  | Andhra Pradesh  | Bihar              |
| Gujarat         | Bihar           | Andhra Pradesh     |
| Bihar           | Haryana         | Chhattisgarh       |

最佳答案

您的表存在一些严重的规范化问题。

话虽如此，并且如果不建议您重组表模式，则可以使用以下方法使用变量获取所需的结果集：

SELECT `Y2006`, `Y2007`, `Y2008`
FROM (
  SELECT State AS `Y2006`, @rn1:=@rn1+1 AS rn1
  FROM mytable
  CROSS JOIN (SELECT @rn1:=0) AS v
  ORDER BY `2006` DESC LIMIT 3
) t1
LEFT JOIN (
   SELECT State AS `Y2007`, @rn2:=@rn2+1 AS rn2
   FROM mytable
   CROSS JOIN (SELECT @rn2:=0) AS v
   ORDER BY `2007` DESC LIMIT 3
) t2 ON t1.rn1 = t2.rn2
LEFT JOIN (
   SELECT State AS `Y2008`, @rn3:=@rn3+1 AS rn3
   FROM mytable
   CROSS JOIN (SELECT @rn3:=0) AS v
   ORDER BY `2008` DESC LIMIT 3
) t3 ON t2.rn2 = t3.rn3

Demo here