SELECT e.id, e.name, e.email, d.name AS department_name, COUNT(p.id) AS project_count FROM employees e JOIN departments d ON e.department_id = d.id LEFT JOIN project_employees pe ON e.id = pe.employee_id LEFT JOIN projects p ON pe.project_id = p.id WHERE e.status = 'ACTIVE' GROUP BY e.id, e.name, e.email, d.name HAVING COUNT(p.id) > 2 ORDER BY project_count DESC;
JPQL
createQuery() 使用JPQL, 用:傳入參數, 所有底線都改成駝峰式命名 Join A B
SELECT e.id, e.name, e.email, d.name AS departmentName, COUNT(pe.project) AS projectCount FROM Employee e JOIN e.department d LEFT JOIN e.projectEmployees pe LEFT JOIN pe.project p WHERE e.status = com.example.Status.ACTIVE GROUP BY e.id, e.name, e.email, d.name HAVING COUNT(p.id) > 2 ORDER BY projectCount DESC