mysql - 在Sql中将每6行转置为column1、2、3等的简单方法？

谢谢您的帮助！我真的不知道该怎么做。

最佳答案

使用PIVOT将行转换为列。

看一下www.artfulsoftware.com/infotree/qrytip.php?id=78示例；

SELECT
  IFNULL(empId,'Totals') AS EmpId,       -- outer query labels rollup row
  sums.2005, sums.2006, sums.2007,       -- and calculates horizontal sums
  sums.2005 + sums.2006 + sums.2007 AS Sums
FROM (                                   -- inner query groups by employee
  SELECT                                 -- with an expression for each column
    EmpID,
    SUM(IF(Yr=2005,sales,0)) As '2005',
    SUM(IF(Yr=2006,sales,0)) As '2006',
    SUM(IF(Yr=2007,sales,0)) As '2007'
  FROM Sales
  GROUP BY EmpID WITH ROLLUP
) AS sums;

+--------+----------+----------+----------+-----------+
| EmpId  | 2005     | 2006     | 2007     | Sums      |
+--------+----------+----------+----------+-----------+
| 1      | 12000.00 | 18000.00 | 25000.00 |  55000.00 |
| 2      | 15000.00 |  6000.00 |     0.00 |  21000.00 |
| 3      |     0.00 | 20000.00 | 24000.00 |  44000.00 |
| Totals | 27000.00 | 44000.00 | 49000.00 | 120000.00 |
+--------+----------+----------+----------+-----------+