NutzCN Logo
问答 自定义sql分页查询到的数据顺序有问题
发布于 810天前 作者 qq_3c03857e 794 次浏览 复制 上一个帖子 下一个帖子
标签:

sql语句执行后,控制台打印的sql语句执行后查到的结果与sql.callback赋值到List中的结果不一致,请问是什么原因造成的

public JSONObject getTable(Pager pager, String startDate, String endDate, String code) {
		JSONObject rst = new JSONObject();
		String str = " select t.drug_code_platform,t.drug_code_gtin,t.drug_code,t.drug_batchno,t.entered_datetime," +
				"t.use_flag,p.drug_name,p.drug_spec,p.units,p.packing,p.manufactor " +
				"from drug_code t left join drug_price p " +
				"on p.drug_code_platform = t.drug_code_platform and p.drug_code_gtin = t.drug_code_gtin "
				+ " where (t.drug_code_platform like '%'||@code||'%' or t.drug_code_gtin like '%'||@code||'%'" +
				" or t.drug_batchno like '%'||@code||'%' or t.drug_code like '%'||@code||'%'" +
				" or p.drug_name like '%'||@code||'%' or p.drug_spec like '%'||@code||'%' or p.drug_price like '%'||@code||'%' or p.units" +
				"  like '%'||@code||'%' or p.drug_form  like '%'||@code||'%' or p.packing  like '%'||@code||'%' or p.manufactor like '%'||@code||'%' )"
				+ " and t.ENTERED_DATETIME BETWEEN @startDate and @endDate "
				+ " order by t.drug_code_platform , t.drug_code_gtin";
		Sql sql = Sqls.create(str);
		sql.setParam("code", code);
		sql.setParam("startDate", Times.D(startDate));
		sql.setParam("endDate", Times.D(endDate+" 23:59:59"));
		pager.setRecordCount((int) Daos.queryCount(dao, sql));
		sql.setPager(pager);
		sql.setCallback(Sqls.callback.records());
		dao.execute(sql);
		List<Record> list = sql.getList(Record.class) == null ? new ArrayList<Record>() : sql.getList(Record.class);
		for(int i=0; i<list.size(); i++){
			Record re = list.get(i);
			System.out.println(re.get("drug_code").toString());
		}
		rst.put("data", list);
		rst.put("pager", pager);
		return ResponseVo.ok(rst);
	}
2 回复

这个是控制台打印出来的sql语句
SELECT * FROM (SELECT T.*, ROWNUM RN FROM (select t.drug_code_platform,t.drug_code_gtin,t.drug_code,t.drug_batchno,t.entered_datetime,t.use_flag,p.drug_name,p.drug_spec,p.units,p.packing,p.manufactor from drug_code t left join drug_price p on p.drug_code_platform = t.drug_code_platform and p.drug_code_gtin = t.drug_code_gtin where (t.drug_code_platform like '%'||''||'%' or t.drug_code_gtin like '%'||''||'%' or t.drug_batchno like '%'||''||'%' or t.drug_code like '%'||''||'%' or p.drug_name like '%'||''||'%' or p.drug_spec like '%'||''||'%' or p.drug_price like '%'||''||'%' or p.units like '%'||''||'%' or p.drug_form like '%'||''||'%' or p.packing like '%'||''||'%' or p.manufactor like '%'||''||'%' ) and t.ENTERED_DATETIME BETWEEN to_date('2021-02-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and to_date('2022-03-01 23:59:59', 'yyyy-MM-dd hh24:mi:ss') order by t.drug_code_platform , t.drug_code_gtin) T WHERE ROWNUM <= 10) WHERE RN > 0

问题找到了,是因为排序的问题

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