数据库查询语句，查询出来数据缺失

问题描述：

执行查询语句，因为网页要分页，所以在外面嵌套了一层分页，里面一层查询的数据是根据id，进行排序的，结果一查询，查出来的大部分数据为空，但是id确是对的。只要把排序字段不是id或者嵌套少掉外面一层分页，数据就能出来。
select rownum RN, tmp2.*
from (
select *
from (select *
from (select *
from SS_MODEL_WSUPPLY_BENEFIT t
where isdel = 0
and id in (select id
from ss_model_wsupply_benefit
where pid = 0)) tb
where 1 = 1 order by id desc
) tmp
where rownum <= 20) tmp2
执行结果：图片说明
正确的结果如下：
网络大神求解，我看到的时候是一脸懵的，因为这个SQL语句是后台生成的，之前很多都是有效的，还是第一次遇到这种问题

答

select rownum RN, tmp.*
from (select *
from SS_MODEL_WSUPPLY_BENEFIT t, ss_model_wsupply_benefit s
where t.isdel = 0
and s.pid = 0
and t.id = s.id
order by t.id desc) tmp where rownum <= 20

            试试这个,用那么多子查询干嘛?

答

 select *
from (select *
from SS_MODEL_WSUPPLY_BENEFIT t
where isdel = 0
and id in (select id
from ss_model_wsupply_benefit
where pid = 0)) tb
where 1 = 1 order by id desc

这层查询完全没必要把这个最外层的select 和 where条件去掉试试

答

select * SS_MODEL_WSUPPLY_BENEFIT where isdel = 0 and pid = 0 limit 1, 20 order by id desc;

答

取了别名在字段前面加上别名试试

数据库查询语句，查询出来数据缺失

相关推荐