java - 标准查询 ORDER BY 产生错误。这是 SQL-SERVER 的限制吗？如何在复杂的条件查询上正确排序？

Question

我有以下条件查询:

    String cat = "H";
    Criteria criteria = currentSession().createCriteria(this.getPersistentClass()).
            add(Restrictions.ne("category", cat)).
            createAlias("employees", "emp").
            createAlias("emp.company", "company");
    Disjunction disjunction = Restrictions.disjunction();
    for(Region r: regions){
           disjunction.add(Restrictions.eq("company.region", r));
    }
    criteria.add(disjunction);

    if(status != null) {
        criteria.add(Restrictions.eq("status", status));
    }

    if (period != null) {
        criteria.add(Restrictions.eq("period", period));
    }

    criteria.setProjection(Projections.groupProperty("id")) //this line was added to try to "fix" the error, but it still happened.
    criteria.addOrder(Order.asc("id"));

我猜解释我的条件查询的查询可能是:

  select n.* from NOMINATION n
  join NOMINEE i on n.NOM_ID = i.NOM_ID
  join EMPLOYEE e on e.EMP_ID = i.EMP_ID
  join COMPANY c on c.COMPANY_CODE = e.COMPANY_CODE
  where n.CATEGORY_CODE!='H' and (c.REGION_ID = ? or c.REGION_ID = ? or c.REGION_ID = ?) and n.STATUS_ID = ? and n.PERIOD_ID = ?
  order by n.NOM_ID

我在这里尝试做的事情非常令人困惑，但在大多数情况下它都有效，除非我添加此特定行(尽管查询工作正常):

 criteria.addOrder(Order.asc("id"));

然后我收到错误:

 java.sql.SQLException: Column "NOMINATION.NOM_ID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

我怀疑这与 SQL-SERVER 有关。我已经按id分组。那么我在这里做错了什么，或者我应该使用 HQL 吗？

Answer 1

您当前的查询似乎是一个简单的查询，没有使用任何分组函数，也没有使用分组查询。根据您当前的要求，您不必使用此行。

criteria.setProjection(Projections.groupProperty("id")).addOrder(Order.asc("id"));

或者你必须修改你的sql语句。