NutzCN Logo
问答 关于分页查询 排序问题
发布于 16天前 作者 qq_5d7ceb9a 110 次浏览 复制 上一个帖子 下一个帖子
标签: nutzwk

我在自定义查询中需要外连接查询 需要排序 就会报错
报错信息:除非另外还指定了 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' 附近有语法错误。

这种情况怎么处理

7 回复

这是代码

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));
}



改成这样写就可以了 但是感觉有点写麻烦了 还不如建了视图

sqlserver?

是的 这个跟数据库有关系吗

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);

SqlServer的自定义sql, 还是自行分页吧, 不支持limit太蛋疼

确实,脑壳疼

添加回复
请先登陆
回到顶部