要做到这一点，首先必须联接表，然后过滤条目，将它们分组，然后对它们进行排序。
因此，您的sql查询应该如下所示：

select team.*
from Team team
  inner join employee
  inner join skill
where skill.active = true and skill.expiryDate > today
group by team.name
order by count(skill.name) desc

旁注：
使用 Specification 在这种情况下，这不是您想要做的，因为它们不代表完整的查询，而是在多个查询中使用的语句或查询的一部分。
使用jpa criteriaquery：

public List<Team> getTeamsWithActiveSkills() {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Team> cq = cb.createQuery(Team.class);
        Root<Team> root = cq.from(Team.class);
        Join<Team, Employee> employees = root.join("employees");
        Join<Team, Skill> skills = employees.join("skills");

        Predicate isActive = cb.isTrue(skills.get("active"));
        Predicate isNonExpired = cb.greaterThan(skills.get("expiryDate"), LocalDate.now());

        cq.where(isActive, isNonExpired).groupBy(root.get("name"));

        Order order = cb.desc(cb.count(skills));
        cq.orderBy(order);

        return em.createQuery(cq).getResultList();
    }

因为我个人觉得criteriaquery很难阅读，而且不直观，所以可以使用querydsl作为替代方法。
使用querydsl：

public List<Team> getTeamsWithActiveSkills() {

        QTeam team = QTeam.team;
        QEmployee employee = QEmployee.employee;
        QSkill skill = QSkill.skill;
        JPQLQuery<Team> query = from(team).join(team.employees, employee).join(employee.skills, skill);

        query = teamJPQLQuery.where(skill.active.isTrue().and(skill.expiryDate.gt(LocalDate.now())));

        query = query .groupBy(team.name);
        query = query .orderBy(skill.name.count().desc());

        return query.fetch();
    }