这个hql语句在hibernate编辑器里运行或者是在程序里运行一切都正常,可是用命名sql查询的时候就会报错:在关键字 'where' 附近有语法错误。
hql语句如下:
select q.qid as qid,
q.askDate as askDate,
q.qtitle as qtitle,
q.qcontent as qcontent,
q.studyUser.uname as askUserName,
a.aid as aid,
a.acontent as askContent,
a.suid as answerUserId,
a.adate as answerDate
from Question q
left join q.answers a
where a.adate is null
or
a.adate in
(
select max(adate)
from Answer
group by question.qid
)
sql命名查询是这样写的:
<sql-query name="qra">
<return alias="qraList" class="com.forum.hiberbean.QRecentAs" />
select
q.qid as qid,
q.askDate as askDate,
q.qtitle as qtitle,
q.qcontent as qcontent,
q.studyUser.uname as askUserName,
a.aid as aid,
a.acontent as askContent,
a.suid as answerUserId,
a.adate as answerDate
from Question q
left join q.answers a
where a.adate is null
or
a.adate in
(
select max(adate)
from Answer
group by question.qid
)
</sql-query>
哪位高人帮我看看啊!
hql语句如下:
select q.qid as qid,
q.askDate as askDate,
q.qtitle as qtitle,
q.qcontent as qcontent,
q.studyUser.uname as askUserName,
a.aid as aid,
a.acontent as askContent,
a.suid as answerUserId,
a.adate as answerDate
from Question q
left join q.answers a
where a.adate is null
or
a.adate in
(
select max(adate)
from Answer
group by question.qid
)
sql命名查询是这样写的:
<sql-query name="qra">
<return alias="qraList" class="com.forum.hiberbean.QRecentAs" />
select
q.qid as qid,
q.askDate as askDate,
q.qtitle as qtitle,
q.qcontent as qcontent,
q.studyUser.uname as askUserName,
a.aid as aid,
a.acontent as askContent,
a.suid as answerUserId,
a.adate as answerDate
from Question q
left join q.answers a
where a.adate is null
or
a.adate in
(
select max(adate)
from Answer
group by question.qid
)
</sql-query>
哪位高人帮我看看啊!
from Question q
left join q.answers a
where a.adate is null
or
a.adate in
(
select max(adate)
from Answer
group by question.qid
) 中的
(
select max(adate)
from Answer
group by question.qid
)
你给解释一下,没有这样的吧,你拿PL/sql 执行一下看看
这个的意思是选择每个问题中回答最早答案的日期,这是hql语句,选择的是对象,和表没有什么关系,所以需要用hibernate的编辑器来运行,用数据库的查询分析器是运行不了的
select * from A left outter join on 条件 外连接怎么会有 where?
select max(adate)
from Answer
group by question.qid
)
括号里应该为select max(adate) from Answer group by Answer.qid 子查询有问题
应该是实际上两个对象question(csdn上的问题)和answer(每个问题都有N个回答,不管是对错,或者答非所问的回复),所以一个question对应多个answer,
现在你想要检索出来,每一个问题的,最后的一个回答的内容,然后联合这个question内容和并在一起显示出来????头大大
你先分开来写,把sql跑起来再说吧
我也看不懂你的sql
studyUser(suid,uName,uPassword,uIden)
question(qid,qtitle,qcontent,fk_uid,askDate)
answer(aid,acontent,aDate,fk_qid,suid)下面是我的sql语句:select *
from question q
left join answer a
on q.qid = a.fk_qid
where (
a.aDate in
(
select max(aDate)
from answer
group by fk_qid
)
)
or
a.aDate is null这个sql语句执行出来是没有问题的,但是由这个修改之后的hql语句,即select q.qid as qid,
q.askDate as askDate,
q.qtitle as qtitle,
q.qcontent as qcontent,
q.studyUser.uname as askUserName,
a.aid as aid,
a.acontent as askContent,
a.suid as answerUserId,
a.adate as answerDate
from Question q
left join q.answers a
where a.adate is null
or
a.adate in
(
select max(adate)
from Answer
group by question.qid
) 在hibernate编辑器里运行或者是在程序里运行一切都正常,可是用命名sql查询的时候就会报错:在关键字 'where' 附近有语法错误。至于where,on的问题,我原来用的是on q.qid=a.aid,但是用这个hibernate会报错:
ERROR main org.hibernate.hql.PARSER - line 4:1: unexpected token: on
我在网上查找了资料,只要将
from Question q left join Answer a on q.qid=a.fk_qid
改成
from Question q left join q.answers a
就不会出现什么问题了,但是很无奈,这个hql语句放在hibernate编辑器或者程序里,它都一切ok,用命名sql查询的时候就出错了。
private String qid;
private StudyUser studyUser;
private String qtitle;
private String qcontent;
private Date askDate;
private Set answers = new HashSet(0);
而这个是相应的映射类中的一部分<id name="qid" type="string">
<column name="qid" length="32" />
<generator class="assigned" />
</id>
<many-to-one name="studyUser"
class="com.forum.hiberbean.StudyUser" fetch="select">
<column name="fk_uid" length="32" not-null="true" />
</many-to-one>
<property name="qtitle" type="string">
<column name="qtitle" length="50" not-null="true" />
</property>
<property name="qcontent" type="text">
<column name="qcontent" not-null="true" />
</property>
<property name="askDate" type="timestamp">
<column name="askDate" length="23" />
</property>
<set name="answers" inverse="true">
<key>
<column name="fk_qid" length="32" />
</key>
<one-to-many class="com.forum.hiberbean.Answer" />
</set>