oracle和MySQL，实现最后一行添加合计，分时段展示数据
是这样一张表：
-- ----------------------------
-- 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
更新-----------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
decode(grouping(TO_CHAR(TIMESTAMP,'hh24')),1,'合计',TO_CHAR(TIMESTAMP,'hh24')) 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
更新-----------------------------------------------------------------------------------------------------------------------------------------------------
oracle演示结果：
MySQL:（用到的是WITH ROLLUP）
SELECT
CASE DATE_FORMAT(TIMESTAMP,'%H')
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 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
WHERE
TIMESTAMP BETWEEN STR_TO_DATE (
'2018-01-01 10:10:10',
'%Y-%m-%d %T'
)
AND STR_TO_DATE (
'2019-01-01 10:10:10',
'%Y-%m-%d %T'
)
GROUP BY DATE_FORMAT(TIMESTAMP,'%H') WITH ROLLUP
ORDER BY time_period;
MySQL结果：
就是有个小问题，MySQL在最后的合计行会把该字段最后的值填进去，我也没有仔细去修改SQL了，应该可以把这个值改过来，我懒得看了，直接在代码里修改了。感觉SQL写的很烂，应该有大神能用更好的方式写出来，希望能得到指点，多谢~！
更新-----------------------------------------------------------------------------------------------------------------------------------------------------
MySQL的grouping函数也可以实现，合计也能显示了
SELECT
case grouping(DATE_FORMAT(TIMESTAMP,'%H'))
when 1 then '合计'
else DATE_FORMAT(TIMESTAMP,'%H') 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 DATE_FORMAT(TIMESTAMP,'%H') WITH ROLLUP
order by time_period
更新-----------------------------------------------------------------------------------------------------------------------------------------------------
而且还有个问题，这种写法没办法区分每一天，如果数据量跨天的话查出来的仍然是按时段划分的，这个我也在想怎么实现，能把每天各时段的数据查出来，需求是这样的，所以这个需求就是没有做完emmmm...
Excel表格的生成：
实现层方法：
public HttpResponseTemp excel(ReportForm report, UserEntity user, HttpServletRequest request, HttpServletResponse response) {
List<ExcelDataDto> list = excelData(report);
String excel_top = "流量统计-时间：" + report.getStart() + " - " + report.getEnd();
// 根据系统的实际情况选择目录分隔符
String separator = File.separator;
//File desktopDir = FileSystemView.getFileSystemView().getHomeDirectory();
String pathSave = "";
if (Platform.isWindows()) {
//获取用户桌面路径
pathSave = FileSystemView.getFileSystemView().getHomeDirectory() + separator + "表格下载";
} else if (Platform.isLinux()) {
//服务器存放路径
SysConfigDto sysConfigDto = sysConfigMapperWrapper.getSysConfig();
pathSave = sysConfigDto.getResource_root() + GlobalConstant.DOWNLOAD;
pathSave = pathSave.replace("/", separator);
}
File f = new File(pathSave);
f.mkdir();
String path = f.getAbsolutePath();
String fileName = TimeUtility.getCurrentTimeForFileName() + ".xls";
String filePath = path + separator + fileName;
ExcelExportUtil.excel(excel_top, filePath, list);
return null;
}Excel工具类方法：
/**
* @param excel_top 表头
* @param filePath 文件生成路径
* @param list 数据
*/
public static void excel(String excel_top, String filePath, List<ExcelDataDto> list) {
GPLogger.info("excel内容：" + list.toString());
WritableWorkbook wwb = null;
OutputStream os = null;
try {
os = new FileOutputStream(filePath);
//response.reset();// 清空输出流
// 设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat = new WritableCellFormat(font);
// 设置背景颜色;
cellFormat.setBackground(Colour.WHITE);
// 设置边框;
cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
// 设置文字居中对齐方式;
cellFormat.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
File name = new File(filePath);
// 创建写工作簿对象
wwb = jxl.Workbook.createWorkbook(name);
//String str = new String(excel_top.getBytes("UTF-8"), "UTF-8");
WritableSheet ws = wwb.createSheet("report", 10);//sheet名
// 设置标题单元格的文字格式
WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat titleFontFormat = new WritableCellFormat(titleFont);
titleFontFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
titleFontFormat.setAlignment(Alignment.CENTRE);
//titleFontFormat.setBackground(Colour.LIGHT_TURQUOISE);
// 设置内容数据单元格的文字格式
WritableFont cellFont = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFontFormat = new WritableCellFormat(cellFont);
cellFontFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
cellFontFormat.setAlignment(Alignment.CENTRE);//文字对齐方式
// 设置内容数据单元格的文字格式
WritableFont cellFont1 = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFontFormat1 = new WritableCellFormat(cellFont1);
cellFontFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
cellFontFormat1.setAlignment(Alignment.CENTRE);//文字对齐方式
ws.getSettings().setDefaultColumnWidth(18);
ws.getSettings().setDefaultRowHeight(300);
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
/*======= 设置列宽 =====*/
ws.setColumnView(0, 30);//根据内容自动设置列宽
ws.setColumnView(1, 30);
ws.setColumnView(2, 30);
ws.setColumnView(3, 24);
ws.setColumnView(4, 24);
ws.setColumnView(5, 24);
//合并首行6列为标题：excel_top 的值
ws.mergeCells(0, 0, 5, 0);//合并单元格 第一个参数从哪个列开始合并，第二个参数为第几行，第三个参数为到那一列结束，第四个参数为合并几行
/*======= 填充标题 =====*/
ws.addCell(new Label(0, 0, excel_top, titleFontFormat)); // 第一个参数为: 第几列，第二个参数为: 第几行
/*======= 填充表头 =====*/
ws.addCell(new Label(0, 1, "时段", titleFontFormat)); // 第一个参数为: 第几列，第二个参数为: 第几行
ws.addCell(new Label(1, 1, "未识别", titleFontFormat));
ws.addCell(new Label(2, 1, "大型车", titleFontFormat));
ws.addCell(new Label(3, 1, "中型车", titleFontFormat));
ws.addCell(new Label(4, 1, "小型车", titleFontFormat));
ws.addCell(new Label(5, 1, "合计", titleFontFormat));
int content_count = 2;//從第三行開始寫入數據
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
if (i == list.size() - 1) {//最后一行数据时将时间段的位置改为“合计”
ws.addCell(new Label(0, content_count, "合计", titleFontFormat));
}else {
ws.addCell(new Label(0, content_count, list.get(i).getTime_period(), cellFontFormat));// 时间段 // new Label参数意义： 列，行，内容，样式（可不传值）
}
ws.addCell(new Label(1, content_count, list.get(i).getUnknown_type().toString(), cellFontFormat));// 未识别
ws.addCell(new Label(2, content_count, list.get(i).getSmall().toString(), cellFontFormat));// 小车
ws.addCell(new Label(3, content_count, list.get(i).getMiddle().toString(), cellFontFormat));// 中车
ws.addCell(new Label(4, content_count, list.get(i).getHuge().toString(), cellFontFormat));// 大车
ws.addCell(new Label(5, content_count, list.get(i).getTotal().toString(), cellFontFormat));// 合计
content_count++;
}
}
ws.toString();
wwb.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (wwb != null) {
wwb.close();
}
if (os != null) {
os.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
下载：
public void get(HttpServletResponse response, String fileName) {
String realPath = "";
// 根据系统的实际情况选择目录分隔符
String separator = File.separator;
if (Platform.isWindows()) {
//获取用户桌面路径
realPath = FileSystemView.getFileSystemView().getHomeDirectory() + separator + "表格下载";
realPath = realPath.replace("\\", "/");
} else if (Platform.isLinux()) {
//服务器存放路径
SysConfigDto sysConfigDto = sysConfigMapperWrapper.getSysConfig();
realPath = sysConfigDto.getResource_root() + GlobalConstant.DOWNLOAD;
realPath = realPath.replace("/", separator);
}
if (fileName != null) {
File file = new File(realPath, fileName);
if (file.exists()) {
//response.setContentType("application/force-download");// 设置强制下载不打开
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
byte[] buffer = new byte[1024];
FileInputStream fis = null;
BufferedInputStream bis = null;
try {
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
GPLogger.info("success");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
}生成的Excel：
主要就是最后一个问题了：用户查询的条件是跨很多天的，但我的数据只能将所有天整合在一起展示，希望有大神会的能叫我一下，非常感谢！