可能的重复:
mysql-行到列
我在mysql中有下表:
原始表
有人知道如何在表上选择以下结果以获取以下结果:
GivenName | FamilyName | EMailHome | PhoneNrHome
--------------------------------------------------------
Angus | Young | angus@gmail.com | (123) 456 789
因此,链接到同一用户ID的字段应在列中安排。
帮助您将非常感谢!
Please try self as per below example :
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 |
+--------+----------+----------+----------+-----------+