buffer.append("select new map(c.cardid as cardid,c.startcost as startcost,r.rulename as rulename,c.goodcode as goodcode,c.provinceid as provinceid,c.cityid as cityid,n.describe as describe) ");
buffer.append("from Cardforsale as c,Numtype as n,Numrule as r,ProductCommend as p ");
buffer.append("where c.ruleid = r.ruleid ");
buffer.append("and r.numtypeid = n.numtypeid ");
buffer.append("and p.goodcode <> c.goodcode ");上面是我SQL的一部分,p.goodcode <> c.goodcode 我加了这个条件之后就检索不到数据了,我的意思是想把p表中没有的goodcode字段的数据检索出来。请教大侠们。
buffer.append("from Cardforsale as c,Numtype as n,Numrule as r,ProductCommend as p ");
buffer.append("where c.ruleid = r.ruleid ");
buffer.append("and r.numtypeid = n.numtypeid ");
buffer.append("and p.goodcode <> c.goodcode ");上面是我SQL的一部分,p.goodcode <> c.goodcode 我加了这个条件之后就检索不到数据了,我的意思是想把p表中没有的goodcode字段的数据检索出来。请教大侠们。
select new map(c.cardid as cardid, c.startcost as startcost, r.rulename as rulename, c.goodcode as goodcode, c.provinceid as provinceid, c.cityid as cityid, n.describe as describe)
from Cardforsale as c, Numtype as n, Numrule as r, ProductCommend as p
where c.ruleid = r.ruleid
and r.numtypeid = n.numtypeid
and p.goodcode not in (select goodcode from Cardforsale);试试上边的。
有个问题一定要清楚, select 出来的东西一般都是 集合, 所以你这里面
p.goodcode <> c.goodcode 会出现什么情况呢?
P.goodCode c.goodcode
1 2
2 3
3 5
4 6
你觉得查出来的结果应该是怎么样的?
看看是不是需要做个 存储过程,可能要用到游标
当然也可以用 子查询来做,不过具体的嘛,会有些难度
本身就是空的,当然检查不出来啊
我的流程是,我把从其他各个表中检索出来的数据添加到ProductCommend 表中,但是如果我之前已经做了一次添加操作,在ProductCommend表中有一条或者多条的添加的数据了,现在我再进行一次检索的时候就是我执行上面我问的那个sql语句的时候,我想检索到的结果里面不包括我刚刚已经添加到ProductCommend 表中的数据。因为如果检索到的结果还包含我已经添加的数据的话,我再次添加的话就会有重复
select new map(c.cardid as cardid, c.startcost as startcost, r.rulename as rulename, c.goodcode as goodcode, c.provinceid as provinceid, c.cityid as cityid, n.describe as describe)
from Cardforsale as c, Numtype as n, Numrule as r, ProductCommend as p
where c.ruleid = r.ruleid
and r.numtypeid = n.numtypeid
and c.goodcode not in (select goodcode from ProductCommend);看看这个能不能解决你的问题
Session session = this.getSession();
Query query = session.createQuery(sql.toString());
query.setParameter(keys[0], values[0]);
query.setFirstResult(pageInfo.getCurrPage() * pageInfo.getNum());
query.setMaxResults(pageInfo.getNum());
pageInfo.setTotalNum(this.count(session, sql.toString(), keys, values));
resultList = query.list();
session.close();
return resultList;是不是这样不能执行带有子查询的sql啊。。我感觉大家的办法是可行的 但是我这边就是查询不到数据