SQL排序返回指定个数结果的VO映射

一、原生SQL

SELECT
*
FROM
(
SELECT
cpointname AS cpointname,
equipname AS equipname,
COUNT(equipname) AS num
FROM
iis_defect_info d
WHERE
d.deptid IN ('37', '38')
AND d.tenantid = 9
GROUP BY
equipname
ORDER BY
num DESC
LIMIT 5
) t
WHERE

t.equipname IS NOT NULL

二、VO实体类

        private String cpointname;
private Integer num;

private String equipname;

三、拼接查询SQL及结果返回

StringBuffer buffer = new StringBuffer();
String[] cells = deptids.split(",");
int i = 0;
for (String id : cells) {
if ((cells.length - 1) == i++) {
buffer.append("'");
buffer.append(id + "'");
} else {
buffer.append("'");
buffer.append(id + "',");
}
}
String sql = "select * from (SELECT cpointname as cpointname ,equipname as equipname,COUNT(equipname) AS num from iis_defect_info d "
+ "WHERE d.deptid in(" + buffer.toString() + ")AND d.tenantid ='9'"
+ "group by equipname order by num desc limit 5)t where t.equipname is not null";
Query query_xjbhgx = em.createNativeQuery(sql);
query_xjbhgx.unwrap(SQLQuery.class).addScalar("cpointname", StandardBasicTypes.STRING)
.addScalar("equipname", StandardBasicTypes.STRING).addScalar("num", StandardBasicTypes.INTEGER)
.setResultTransformer(Transformers.aliasToBean(Defectinfovo.class));

List<Defectinfovo> tasklst = query_xjbhgx.getResultList();

注意：只能是'37', '38'，否则出错