小弟新手,现在遇到分组问题,求,JPA或者Hibernate的查询语句,谢谢
现有如下表:CREATE TABLE `mailtemplate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mailContent` text COMMENT '模板内容',
`mailType` int(11) DEFAULT NULL COMMENT '邮件内容',
`lastEditTime` datetime DEFAULT NULL COMMENT '最后一次编辑日期',
`orderNumber` int(11) DEFAULT NULL COMMENT '排列序号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8要求:
以mailType字段分组,并且提取每个分组orderNumber字段为最小值的所有记录,
现有如下表:CREATE TABLE `mailtemplate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mailContent` text COMMENT '模板内容',
`mailType` int(11) DEFAULT NULL COMMENT '邮件内容',
`lastEditTime` datetime DEFAULT NULL COMMENT '最后一次编辑日期',
`orderNumber` int(11) DEFAULT NULL COMMENT '排列序号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8要求:
以mailType字段分组,并且提取每个分组orderNumber字段为最小值的所有记录,
(
where select min(orderNumber) from mailtemplate group by mailType
)
List list = query.setFirstResult(参数).setMaxResult(参数);
from mailtemplate a,
(select min(m.orderNumber) as orderN, m.mailType as mailT
from mailtemplate m
group by m.mailType) b
where a.orderNumber = b.orderN
and a.mailType = b.mailT
select mt.* from mailtemplate as mt
inner join (
select min(orderNumber) min_num,mailType from mailtemplate group by mailType
) t
on mt.orderNumber=t.min_num and mt.mailType = t.mailType;