[MySQL]典型的行列转换

列变成行

测试数据库数据样式：

应用的sql语句：

SELECT TM,NAME,SUM(GE) AS 'GE',SUM(GD) AS 'GD',SUM(CT) AS 'CT',SUM(NUM) AS 'NUM'
FROM 
	(
	SELECT a.tm,a.Name,
	(CASE WHEN DH='GE' THEN 1 ELSE 0 END) AS 'GE',
	(CASE WHEN DH='GD' THEN 1 ELSE 0 END) AS 'GD',
	(CASE WHEN DH='CT' THEN 1 ELSE 0 END) AS 'CT',
	(CASE WHEN DH='GE' THEN 0
				WHEN DH='GD' THEN 0
				WHEN DH='CT' THEN 0
				ELSE 1 END) AS 'NUM'
	 FROM
		(
			SELECT DATE_FORMAT(UploadTime,"%Y-%m-%d") AS 'TM',depot.`Name`,LEFT(WaybillNo,2) AS 'DH'
			FROM waybill,depot
			WHERE waybill.DepotId = depot.Id
		) AS a
	) AS b
GROUP BY TM,NAME
ORDER BY TM,NAME

　　

运行后的结果：