Em viết câu câu query trong class imp thì nó ko chấp nhận if trong count vậy em nên sử lý thế nào?
public List<Object[]> findSectorByIdProject(int id) {
List<Object[]> result=entityManager.createQuery(
"SELECT " +
"s.idSector, " +
"s.name, " +
"COUNT(IF(v.status=0,1,null)) AS sold, " +
"COUNT(IF(v.status=1,1,null)) AS unsold, " +
"COUNT(IF(v.status=2,1,null)) AS locked " +
"FROM Project p,Sector s,Villa v " +
"WHERE " +
"p.idProject=:id and " +
"p.idProject=s.idProject and " +
"s.idSector=v.idSector " +
"GROUP BY s.idSector")
.setParameter("id", id)
.getResultList();
return result;
}
Bản ngắn gọn
public List<Object[]> findSectorByIdProject(int id) {
List<Object[]> result=entityManager.createQuery("SELECT s.idSector,s.name,COUNT(IF(v.status=0,1,null))AS sold,COUNT(IF(v.status=1,1,null))as unsold,COUNT(IF(v.status=2,1,null)) as locked from Project p,Sector s,Villa v where p.idProject=:id and p.idProject=s.idProject and s.idSector=v.idSector GROUP BY s.idSector").setParameter("id",id).getResultList();
return result;
}