NutzCN Logo
问答 自定义sql为什么没排序效果
发布于 159天前 作者 qq_7fafbecc 176 次浏览 复制 上一个帖子 下一个帖子
标签:

代码:

	public NutMap queryBagBloodAgainByParam(NutMap paramMap,Pager pager) {
		NutMap returnMap = new NutMap();
		List<DtoBag> rval = new ArrayList<DtoBag>();
		String sqlstr = "select b.*,a.XYCODE,a.BDCODE,a.SPEC,a.UNIT,a.SOURCE,a.XYER,a.COLLECTIONTIME,a.VALIDDATETIME,"
				+ "(select userName from blood_dict_user c where c.userId = b.oprUser) OPRUSERTEXT, "
				+ "(select ABO  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) ABOPZ, "
				+ "(select RHD  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) RHDPZ, "
				+ "(select COUNT(1) from BLOOD_DATA_BLOODSTORAGE x where x.BAGCODE = a.BAGCODE ) STORAGENUM " 
				+ "from BLOOD_DATA_BAGINFO a,BLOOD_DATA_BAGBLOODAGAIN b where a.BAGCODE = b.BAGCODE ";
		//条件1
		if(paramMap.get("cndBagCode")!=null && !"".equals(paramMap.get("cndBagCode").toString().trim())){
			String cndBagCode = paramMap.get("cndBagCode").toString().trim();
			sqlstr = sqlstr + " and a.bagCode like '%"+cndBagCode+"%'";
		}

		//条件2
		if(paramMap.get("cndOprTime")!=null && !"".equals(paramMap.get("cndOprTime").toString().trim())){
			String cndOprTime = paramMap.get("cndOprTime").toString().trim();
			if(Globals.CND_DATETYPE_1D.equals(cndOprTime)){
				//当天
				sqlstr = sqlstr + " and DateDiff(dd,b.oprTime,getdate()) = 0 ";
			}else if(Globals.CND_DATETYPE_3D.equals(cndOprTime)){
				//3天
				sqlstr = sqlstr + " and DateDiff(dd,b.oprTime,getdate()) <= 3 ";
			}else if(Globals.CND_DATETYPE_1W.equals(cndOprTime)){
				//7天
				sqlstr = sqlstr + " and DateDiff(dd,b.oprTime,getdate()) <= 7 ";
			}else if(Globals.CND_DATETYPE_1M.equals(cndOprTime)){
				//30天
				sqlstr = sqlstr + " and DateDiff(dd,b.oprTime,getdate()) <= 30 ";
			}else{
				//365天
				sqlstr = sqlstr + " and DateDiff(dd,b.oprTime,getdate()) <= 365 ";
			}
		}

		long total = Daos.queryCount(dao,sqlstr);
		returnMap.put("total", total);
		sqlstr = sqlstr + " order by a.inTime desc ";
		Sql sql = Sqls.create(sqlstr);
		sql.setPager(pager);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao.getEntity(DtoBag.class));
		dao.execute(sql);
		rval = sql.getList(DtoBag.class);
		returnMap.put("rows", rval);
		return returnMap;
	}

后台信息:

select * from(select row_number()over(order by __tc__)__rn__,* from(select top 50 0 __tc__,  
b.*,a.XYCODE,a.BDCODE,a.SPEC,a.UNIT,a.SOURCE,a.XYER,a.COLLECTIONTIME,a.VALIDDATETIME,
(select userName from blood_dict_user c where c.userId = b.oprUser) OPRUSERTEXT, 
(select ABO  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) ABOPZ, 
(select RHD  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) RHDPZ,
(select COUNT(1) from BLOOD_DATA_BLOODSTORAGE x where x.BAGCODE = a.BAGCODE ) STORAGENUM 
from BLOOD_DATA_BAGINFO a,BLOOD_DATA_BAGBLOODAGAIN b where a.BAGCODE = b.BAGCODE  order by a.inTime desc )t)tt where __rn__ > 0

没有排序效果,本应把2018-5-14的记录排在前面,但是没有

4 回复

by a.inTime的确不是主键,可我的需求,不是按主键排血啊

刚才把a表的inTime字段,设为了索引, 还是order by a.inTime
还是没达到预期效果

用两个排序字段

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