List<ProblemDemand> rval = new ArrayList<ProblemDemand>();
String sqlstr = "select a.*,"
+ "(select b.codename from CODEMNG b where b.CODETYPE = 'PDTYPE' and b.CODE = a.pdtype) PDTYPETEXT,"
+ "(select b.codename from CODEMNG b where b.CODETYPE = 'ISEMERGENCY' and b.CODE = a.ISEMERGENCY) ISEMERGENCYTEXT,"
+ "(select b.PROJECTNAME from PROJECT b where b.PROJECTID = a.projectid) projectName, "
+ "(select b.DISPLAYNAME from SYSUSER b where b.userid = a.CREATER) CREATERNAME,"
+ "(select b.DISPLAYNAME from SYSUSER b where b.userid = a.HANDLER) HANDLERNAME,"
+ "(select b.codename from CODEMNG b where b.CODETYPE = 'PDSTATE' and b.CODE = a.pdstate) PDSTATETEXT,"
+ "(select b.codename from CODEMNG b where b.CODETYPE = 'PRODUCTTYPE' and b.CODE = a.productType) PRODUCTTYPETEXT,"
+ "(select b.DISPLAYNAME from SYSUSER b where b.userid = a.CURRENTOPERATOR) CURRENTOPERATORNAME,"
+ "(select b.codename from CODEMNG b where b.CODETYPE = 'ISTEST' and b.CODE = a.istest) ISTESTTEXT,"
+ "(select b.DISPLAYNAME from SYSUSER b where b.userid = a.TESTER) TESTERNAME,"
+ "(select b.codename from CODEMNG b where b.CODETYPE = 'ISFINISH' and b.CODE = a.ISFINISH) ISFINISHTEXT "
+ "from PROBLEMDEMAND a";
String _pdId = pdId.trim();
if(_pdId!=null && !"".equals(_pdId)){
sqlstr = sqlstr + " where a.pdid = '"+_pdId+"'";
}
sqlstr = sqlstr + " order by a.pdId desc";
Sql sql = Sqls.create(sqlstr);
//分页
sql.setPager(pager);
sql.setCallback(Sqls.callback.entities());
sql.setEntity(dao.getEntity(ProblemDemand.class));
dao.execute(sql);
//如何在这种自定义SQL中，把不带分页的的总记录数求出来？？
//因为不用setRecordCount()把总记录数放进pager的话，pager中的total就一直为0，例如 size: 10, total: 0, page: 1/0
//pager.setRecordCount(dao.count(Userinfo.class, cnd));
rval = sql.getList(ProblemDemand.class);
return rval;
7 回复
@wendal Daos.queryCount行不通啊
long total = Daos.queryCount(dao,sqlstr);
pager.setRecordCount((int)total);
2017-07-18 23:28:51,922 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from(select row_number()over(order by __tc__)__rn__,* from(select top 10 0 __tc__, a.*,(select b.codename from CODEMNG b where b.CODETYPE = 'PDTYPE' and b.CODE = a.pdtype) PDTYPETEXT,(select b.codename from CODEMNG b where b.CODETYPE = 'ISEMERGENCY' and b.CODE = a.ISEMERGENCY) ISEMERGENCYTEXT,(select b.PROJECTNAME from PROJECT b where b.PROJECTID = a.projectid) projectName, (select b.DISPLAYNAME from SYSUSER b where b.userid = a.CREATER) CREATERNAME,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.HANDLER) HANDLERNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'PDSTATE' and b.CODE = a.pdstate) PDSTATETEXT,(select b.codename from CODEMNG b where b.CODETYPE = 'PRODUCTTYPE' and b.CODE = a.productType) PRODUCTTYPETEXT,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.CURRENTOPERATOR) CURRENTOPERATORNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'ISTEST' and b.CODE = a.istest) ISTESTTEXT,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.TESTER) TESTERNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'ISFINISH' and b.CODE = a.ISFINISH) ISFINISHTEXT from PROBLEMDEMAND a order by a.pdId desc)t)tt where __rn__ > 0
2017-07-18 23:28:59,659 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select count(1) from (select a.*,(select b.codename from CODEMNG b where b.CODETYPE = 'PDTYPE' and b.CODE = a.pdtype) PDTYPETEXT,(select b.codename from CODEMNG b where b.CODETYPE = 'ISEMERGENCY' and b.CODE = a.ISEMERGENCY) ISEMERGENCYTEXT,(select b.PROJECTNAME from PROJECT b where b.PROJECTID = a.projectid) projectName, (select b.DISPLAYNAME from SYSUSER b where b.userid = a.CREATER) CREATERNAME,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.HANDLER) HANDLERNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'PDSTATE' and b.CODE = a.pdstate) PDSTATETEXT,(select b.codename from CODEMNG b where b.CODETYPE = 'PRODUCTTYPE' and b.CODE = a.productType) PRODUCTTYPETEXT,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.CURRENTOPERATOR) CURRENTOPERATORNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'ISTEST' and b.CODE = a.istest) ISTESTTEXT,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.TESTER) TESTERNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'ISFINISH' and b.CODE = a.ISFINISH) ISFINISHTEXT from PROBLEMDEMAND a order by a.pdId desc)as _nutz_tmp_1tu6uqk7q0gbpqv80jmsovvvke
2017-07-18 23:28:59,809 org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:97) DEBUG - SQLException
com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4874)
怎么贴得这么乱？
select count(1) from (select a.*,(select b.codename from CODEMNG b where b.CODETYPE = 'PDTYPE' and b.CODE = a.pdtype) PDTYPETEXT,(select b.codename from CODEMNG b where b.CODETYPE = 'ISEMERGENCY' and b.CODE = a.ISEMERGENCY) ISEMERGENCYTEXT,(select b.PROJECTNAME from PROJECT b where b.PROJECTID = a.projectid) projectName, (select b.DISPLAYNAME from SYSUSER b where b.userid = a.CREATER) CREATERNAME,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.HANDLER) HANDLERNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'PDSTATE' and b.CODE = a.pdstate) PDSTATETEXT,(select b.codename from CODEMNG b where b.CODETYPE = 'PRODUCTTYPE' and b.CODE = a.productType) PRODUCTTYPETEXT,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.CURRENTOPERATOR) CURRENTOPERATORNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'ISTEST' and b.CODE = a.istest) ISTESTTEXT,(select b.DISPLAYNAME from SYSUSER b where b.userid = a.TESTER) TESTERNAME,(select b.codename from CODEMNG b where b.CODETYPE = 'ISFINISH' and b.CODE = a.ISFINISH) ISFINISHTEXT from PROBLEMDEMAND a order by a.pdId desc)as _nutz_tmp_1tu6uqk7q0gbpqv80jmsovvvke
添加回复
请先登陆