oracle和MySQL，实现最后一行添加合计，分时段展示数据，Excel表格生成，swagger下载表格乱码解决

最新推荐文章于 2022-12-15 09:12:53 发布

原创

最新推荐文章于 2022-12-15 09:12:53 发布 · 2.1k 阅读

5 ·

CC 4.0 BY-SA版权

博客介绍了如何使用Oracle和MySQL的SQL查询，按小时段分组并显示数据总和，同时生成Excel表格。涉及到rollup和grouping函数，以及解决swagger下载Excel乱码的问题。

是这样一张表：

-- ----------------------------
-- Table structure for tbl_radar_statisticsdata
-- ----------------------------
DROP TABLE IF EXISTS `tbl_radar_statisticsdata`;
CREATE TABLE `tbl_radar_statisticsdata` (
`id` int(22) NOT NULL AUTO_INCREMENT,
`deviceno` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT NULL,
`measno` int(11) DEFAULT NULL,
`laneno` int(11) DEFAULT NULL,
`coilno` int(11) DEFAULT NULL,
`headway` float DEFAULT NULL,
`gap` float DEFAULT NULL,
`speed85` float DEFAULT NULL,
`avspeed` float DEFAULT NULL,
`occupancy` float DEFAULT NULL,
`volume` int(11) DEFAULT NULL,
`volume1` int(11) DEFAULT NULL,
`volume2` int(11) DEFAULT NULL,
`volume3` int(11) DEFAULT NULL,
`volume4` int(11) DEFAULT NULL,
`volume5` int(11) DEFAULT NULL,
`deviceid` int(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `time_idx` (`timestamp`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3470450 DEFAULT CHARSET=utf8;

数据大概是这样：

需求是将表内容分时段查询出来，最后一行有合计，并且生成Excel表格，Excel的生成下载就不说了，后面贴上去防止忘记了，主要记一下SQL，因为数据库不确定，所以oracle和MySQL的都写了。

oracle：（用到了grouping和rollup）

               SELECT
decode(grouping(TO_CHAR(TIMESTAMP,'hh24')),1,'合计',
               case
               TO_CHAR(TIMESTAMP,'hh24')
when '00' then '0' when '01' then '1' when '02' then '2' when '03' then '3' when '04' then '4' when '05' then '5' when '06' then '6' when '07' then '7'
when '08' then '8' when '09' then '9' when '10' then '10' when '11' then '11' when '12' then '12' when '13' then '13' when '14' then '14' when '15' then '15'
when '16' then '16' when '17' then '17' when '18' then '18' when '19' then '19' when '20' then '20' when '21' then '21' when '22' then '22' when '23' then '23' end
               ) AS time_period,
               --),
(SUM(volume1)+SUM(volume2)) AS unknown_type,
SUM (volume3) AS small,
SUM (volume4) AS middle,
SUM (volume5) AS huge,
SUM (VOLUME) AS total
FROM
TBL_RADAR_STATISTICSDATA a
WHERE 1=1
group by rollup(TO_CHAR(TIMESTAMP,'hh24'))
               order by time_period

更新-----------------------------------------------------------------------------------------------------------------------------------------------------

最低0.47元/天解锁文章