使用Group By 语句进行聚合操作时输出的方式都是以行的方式呈现,例如:

SELECT T.MESSAGE_TYPE AS MESSAGE_TYPE, COUNT(1) AS COUNT
FROM TABLE_A T
WHERE T.CREATED_AT = '2019-03-22 10:20:20'
GROUP BY T.MESSAGE_TYPE;
效果图

但是通常我们更希望以列的方式展现在表格中,这样更为直观,那么可以改写成如下SQL:

SELECT COUNT(DECODE(T.MESSAGE_TYPE, 'AT+CMD', 1, NULL)) AS AT_CMD,
COUNT(DECODE(T.MESSAGE_TYPE, 'AT+CFG', 1, NULL)) AS AT_CFG,
COUNT(DECODE(T.MESSAGE_TYPE, 'AT+QRY', 1, NULL)) AS AT_QRY
FROM TABLE_A T
WHERE T.CREATED_AT = '2019-03-22 10:20:20';

效果图

打赏
列的方式聚合同一字段不同的值

发表评论