我在自定义查询中需要外连接查询 需要排序 就会报错
报错信息:除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表中无效
加上 top 100 percent 的话 又会报以下错误
PreparedStatement:
'select * from(select row_number()over(order by __tc__)__rn__,* from(select top 10 0 __tc__, top 100 percent a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id where a.qid ='999cde6aa6ab442fa0896a6512221784' order by a.id desc )t)tt where __rn__ > 0'
CaseMessage=关键字 'top' 附近有语法错误。
这种情况怎么处理
这是代码
String s = "";
s += " where a.qid ='" + Toolkit.uid() + "' ";
s += " order by a.id desc ";
Pagination pagelist = userService.listPage(curPage, pageSize,
Sqls.create("SELECT top 100 percent a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id $s").setVar("s", s));
return pagelist;
String s = "";
s += " select a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id";
s += " where a.qid ='" + Toolkit.uid() + "' ";
s += " order by id desc ";
Sql sql = Sqls.create(s);
String s1 = "";
s1 += " select a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id";
s1 += " where a.qid ='" + Toolkit.uid() + "' ";
Sql sql1 = Sqls.create(s1);
return qdService.listPage1(curPage, pageSize, sql, sql1);
/**
* 分页查询(sql1)
*
* @param pageNumber
* @param pageSize
* @param sql
* @return
*/
public Pagination listPage1(Integer pageNumber, int pageSize, Sql sql, Sql sql1) {
pageNumber = getPageNumber(pageNumber);
pageSize = getPageSize(pageSize);
Pager pager = this.dao().createPager(pageNumber, pageSize);
pager.setRecordCount((int) queryCount(dao(), sql1.toString()));// 记录数需手动设置
sql.setPager(pager);
sql.setCallback(Sqls.callback.records());
dao().execute(sql);
return new Pagination(pageNumber, pageSize, pager.getRecordCount(), sql.getList(Record.class));
}
改成这样写就可以了 但是感觉有点写麻烦了 还不如建了视图
String s = "";
s += " select a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id";
s += " where a.qid ='" + Toolkit.uid() + "' ";
s += " order by id desc ";
Sql sql = Sqls.create(s);
String s1 = "";
s1 += " select a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id";
s1 += " where a.qid ='" + Toolkit.uid() + "' ";
Sql sql1 = Sqls.create(s1);
return qdService.listPage1(curPage, pageSize, sql, sql1);
出现分页问题,会把所有记录全查出来,找了一下,需要把select前的空格去掉,不知道什么原理
String s = "";
s += "select a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id";
s += " where a.qid ='" + Toolkit.uid() + "' ";
s += " order by id desc ";
Sql sql = Sqls.create(s);
String s1 = "";
s1 += "select a.* ,b.username as qname FROM qd a left join sys_user b on a.qid = b.id";
s1 += " where a.qid ='" + Toolkit.uid() + "' ";
Sql sql1 = Sqls.create(s1);
return qdService.listPage1(curPage, pageSize, sql, sql1);