列转行然后合并

原始数据

转化后的数据

源代码：

SELECT
recordbytunnel.ttype,
recordbytunnel.tsequense,
tunnelId,
MAX(CASE DataType WHEN 1 THEN RowCount ELSE 0 END) AS '发送总数Cmpp',
MAX(CASE DataType WHEN 2 THEN RowCount ELSE 0 END) AS '发送总数Http',
MAX(CASE DataType WHEN 3 THEN RowCount ELSE 0 END) AS '发送成功总数Cmpp',
MAX(CASE DataType WHEN 4 THEN RowCount ELSE 0 END) AS '发送成功总数Http',

MAX(CASE DataType WHEN 5 THEN RowCount ELSE 0 END) AS '发送失败总数Cmpp',
MAX(CASE DataType WHEN 6 THEN RowCount ELSE 0 END) AS '发送失败总数Http',
MAX(CASE DataType WHEN 7 THEN RowCount ELSE 0 END) AS '送达成功总数Cmpp',
MAX(CASE DataType WHEN 8 THEN RowCount ELSE 0 END) AS '送达成功总数Http',

MAX(CASE DataType WHEN 9 THEN RowCount ELSE 0 END) AS '送达失败总数Cmpp',
MAX(CASE DataType WHEN 10 THEN RowCount ELSE 0 END) AS '送达失败总数Http'

FROM
recordbytunnel
group by tunnelId

然后合并或形成新的行

代码：

SELECT
recordbytunnel.ttype,
recordbytunnel.tsequense,
tunnelId,
MAX(CASE DataType WHEN 1 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 2 THEN RowCount ELSE 0 END) as 发送总数,
MAX(CASE DataType WHEN 3 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 4 THEN RowCount ELSE 0 END) AS '发送成功总数',
MAX(CASE DataType WHEN 5 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 6 THEN RowCount ELSE 0 END) AS '发送失败总数',
MAX(CASE DataType WHEN 7 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 8 THEN RowCount ELSE 0 END) AS '送达成功总数',
MAX(CASE DataType WHEN 9 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 10 THEN RowCount ELSE 0 END) AS '送达失败总数',
(
MAX(CASE DataType WHEN 1 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 2 THEN RowCount ELSE 0 END)-
(MAX(CASE DataType WHEN 7 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 8 THEN RowCount ELSE 0 END))-
(MAX(CASE DataType WHEN 9 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 10 THEN RowCount ELSE 0 END))
) as '送达未知状态总数'
FROM
recordbytunnel
group by tunnelI

新增了2月6日的数据，通道是332，此时需要汇总各个通道的数据

源代码：

SELECT ttype,tsequense,tunnelId,sum(发送总数),sum(发送成功总数),sum(发送失败总数),sum(送达成功总数),sum(送达失败总数),sum(送达未知状态总数) from
(
	SELECT SendDay,ttype,tsequense,tunnelId,
	MAX(CASE DataType WHEN 1 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 2 THEN RowCount ELSE 0 END) as 发送总数,
	MAX(CASE DataType WHEN 3 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 4 THEN RowCount ELSE 0 END) AS 发送成功总数,
	MAX(CASE DataType WHEN 5 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 6 THEN RowCount ELSE 0 END) AS 发送失败总数,
	MAX(CASE DataType WHEN 7 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 8 THEN RowCount ELSE 0 END) AS 送达成功总数,
	MAX(CASE DataType WHEN 9 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 10 THEN RowCount ELSE 0 END) AS 送达失败总数,
	(
	MAX(CASE DataType WHEN 1 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 2 THEN RowCount ELSE 0 END)-
	(MAX(CASE DataType WHEN 7 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 8 THEN RowCount ELSE 0 END))-
	(MAX(CASE DataType WHEN 9 THEN RowCount ELSE 0 END)+MAX(CASE DataType WHEN 10 THEN RowCount ELSE 0 END))
	) as 送达未知状态总数
	FROM
	recordbytunnel
	GROUP BY SendDay,tunnelId
)
temp1
GROUP BY tunnelId