SQL

记一次SQL查询语句

SQL的行转列

Posted by mengxun on May 1, 2018

由来

项目里有个表结构就是类似下图这种表结构:

需求是:根据时间戳(timestamp)进行分组,然后输出这个时间戳状态(status)为hitmiss请求数的和和流量的和,返回json效果类似下面:

[
    {
        "timestam": 1527782400,
        "hit": 24,
        "hitFlow": 241,
        "miss": 25,
        "missFlow": 251
    }
     {
        "timestam": 1527782700,
        "hit": 26,
        "hitFlow": 261,
        "miss": 27,
        "missFlow": 271
    }
    ...
    
]

问题

这种需求如果只是单纯的GROUP BY的话,是可以求出每个时间戳下的请求以及流量的和。但是得到的和是该时间戳下所有status的总和,并不是拆成hit和miss的分别的和。

解决

其实这种问题就是sql中的行转列的问题,用如下语句即可得到想要的效果:

SELECT
	a.hit AS hit,
	a.hitflow AS hitflow,
	b.miss AS miss,
	b.missflow AS missflow,
	a.timestamp as timestamp
FROM
	(
		SELECT
			sum(request) AS hit,
			sum(flow) AS hitflow,
			timestamp
		FROM
			statustable
		WHERE
			status = 'hit'
GROUP BY timestamp
	) AS a,
	(
		SELECT
			sum(request) AS miss,
			sum(flow) AS missflow,
			timestamp
		FROM
			statustable
		WHERE
			status = 'miss'
GROUP BY timestamp
	) AS b WHERE a.timestamp=b.timestamp;