原生SQL分组查询

SELECT
	t.time,
	COUNT(t.time) count
FROM
	(
		SELECT
			substring(alarm.occurtime, 1, 10) time,
			alarm.equiprecorddwid dwid,
			alarm.alarmmode,
			alarm.equiprecorditemname
		FROM
			`eqm_alarm_eventrecord` alarm
		WHERE
			alarm.occurtime BETWEEN "2018-03-15 00:00:00"
		AND "2018-03-16 23:59:59"
		AND alarm.alarmlevel = 1
		AND alarm.tenantid = 9
		GROUP BY
			substring(alarm.occurtime, 1, 10),
			alarm.equiprecorddwid,
			alarm.alarmmode,
			alarm.equiprecorditemname
	) t
GROUP BY
	t.time

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Calendar now = Calendar.getInstance();
		String endTime = sdf.format(now.getTime());
		now.add(Calendar.DAY_OF_MONTH, -6);
		String startTime = sdf.format(now.getTime());

		String sql = "SELECT t.time,COUNT(t.time) count FROM( SELECT substring(alarm.occurtime,1,10) time, alarm.equiprecorddwid dwid, alarm.alarmmode, alarm.equiprecorditemname FROM `eqm_alarm_eventrecord` alarm WHERE alarm.occurtime BETWEEN '"
				+ startTime + " 00:00:00 'AND '" + endTime + " 23:59:59' AND alarm.alarmlevel = " + alarmlevel
				+ " AND alarm.tenantid = " + tenantid
				+ " GROUP BY substring(alarm.occurtime,1,10), alarm.equiprecorddwid, alarm.alarmmode, alarm.equiprecorditemname ) t GROUP BY t.time";
		Query query = em.createNativeQuery(sql);
		query.unwrap(SQLQuery.class).addScalar("time", StandardBasicTypes.STRING)
				.addScalar("count", StandardBasicTypes.INTEGER)
				.setResultTransformer(Transformers.aliasToBean(AlarmEventRecordVo.class));
		@SuppressWarnings("unchecked")
		List<AlarmEventRecordVo> list = query.getResultList();