Carson_huge
2012-12-06 14:46
采纳率: 0%
浏览 395
已采纳

sql多表联合查询子表排序问题,在sql中查询可以,用hibernate查询报错

[code="java"]
select
awordapply0_.ID as col_0_0_,
awordapply0_.PROJECT_CODE as col_1_0_,
awordapply0_.PROJECT_NAME as col_2_0_,
awordapply0_.DEPARTMENT_NAME as col_3_0_,
dicawordty1_.NAME as col_4_0_,
awordapply0_.AWORD_CLASS as col_5_0_,
dicprofess2_.IF_SCORE as col_6_0_,
dicprofess2_.IS_PUB_SCORE as col_7_0_,
(select
max(awordpssco4_.project_score)
from
AWORD_PS_SCORE awordpssco4_
where
awordpssco4_.APPLY_ID=awordapply0_.ID
and awordpssco4_.EXPERT_ID='2c9180073b68e932013b69ba10f60005'
and awordpssco4_.STAGE_ID='2c9180073455647b0139add58be60002') as col_8_0_,
(select
count(*)
from
AWORD_PS_SCORE_ITEM awordpssco5_
where
awordpssco5_.aword_apply_id=awordapply0_.ID
and awordpssco5_.project_score_expert_id='2c9180073b68e932013b69ba10f60005'
and awordpssco5_.module_id='2c9180073455647b0139add58be60002') as col_9_0_,
awordapply0_.FIRST_PS_RESULT as col_10_0_,
awordapply0_.RECOMMEND_CODE as col_11_0_
from
AWORD_APPLY awordapply0_,
DIC_AWORD_TYPE dicawordty1_,
DIC_PROFESSION_GROUP dicprofess2_
where
awordapply0_.AWORD_CLASS=dicawordty1_.ID
and awordapply0_.SECOND_PROFESSION_GROUP_ID=dicprofess2_.ID
and awordapply0_.YEAR='2012'
and awordapply0_.STATE='2'
and awordapply0_.FORM_CHECK_RESULT='1'
and awordapply0_.SECOND_PROFESSION_GROUP_ID='ff8080812d2be631012d2c3003d20001'
and awordapply0_.AWORD_Type='ff8080812bcdd3e9012bcdfa89690006'
and (
awordapply0_.ID not in (
select
awordpshbs6_.AWORD_APPLY_ID
from
AWORD_PS_HB_SCORE awordpshbs6_
where
awordpshbs6_.EXPERT_ID='2c9180073b68e932013b69ba10f60005'
and awordpshbs6_.AWORD_PS_STAGE_ID='2c9180073455647b0139add58be60002'
)
)
order by
(select
max(awordpssco4_.project_score)
from
AWORD_PS_SCORE awordpssco4_
where
awordpssco4_.APPLY_ID=awordapply0_.ID
and awordpssco4_.EXPERT_ID='2c9180073b68e932013b69ba10f60005'
and awordpssco4_.STAGE_ID='2c9180073455647b0139add58be60002')
[/code]

注意:主要是最后的order by 在hibernate中应该怎么写合理?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • iteye_5246 2012-12-06 14:49
    已采纳

    哥们用这个吧
    List cats = sess.createSQLQuery("select * from cats") .addEntity(Cat.class).list();

    createSQLQuery 接受一个sql语句。

    点赞 评论
  • liuyinhuan0409 2012-12-06 14:59

    粗略看了一下你的order by是不是前面的查询结果col_8_0_呀?
    code="sql"
    from AWORD_PS_SCORE awordpssco4_
    where awordpssco4_.APPLY_ID = awordapply0_.ID
    and awordpssco4_.EXPERT_ID = '2c9180073b68e932013b69ba10f60005'
    and awordpssco4_.STAGE_ID = '2c9180073455647b0139add58be60002') as col_8_0_
    [/code]
    如果是的话,为什么不直接 order by col_8_0_呀,没必要order by里面再重新写一次了。。。
    sql查询的执行顺序是:
    (1)FROM JOIN ON

    (2)WHERE
    (3)GROUP BY
    (4)HAVING
    (5)SELECT DISTINCT TOP()

    (6)ORDER BY

    order by的时候别名已经存在,可直接用

    点赞 评论
  • FangXingXing007 2012-12-06 17:23

    hql是Hibernate能够处理的特殊语句,其中的表实际上应该是hibernate Model的bean名称
    例如数据库表名为a
    生成的实体为A
    那你的hql语句里应该是
    from A where A.id
    而不是sql的
    from a where a.id
    这样Hibernate貌似无法识别

    点赞 评论

相关推荐 更多相似问题