select *
from (select t0.*,
row_number() OVER(order by t0.decTime desc) as RowNumber
from (SELECT distinct q.*
from (SELECT p.SEQ_NO as seqNo,
p.LIST_NO as listNo,
p.GOODS_NO as goodsNo,
p.CODE_TS as codeTs,
to_char(p.UPDATE_TIME, 'yyyy-mm-dd HH:mm:ss') as updateTime,
to_char(p.DEC_TIME, 'yyyy-mm-dd') as decTime
FROM (SELECT *
FROM PRE_GOODS_LIST
Union All
SELECT * FROM CUR_GOODS_LIST) p
WHERE p.SEQ_NO = '99212236-1b10-4cb4-bf01-37ba7c360201'
order by updatetime desc) q) t0) t
where t.RowNumber between 1 and 15
from (select t0.*,
row_number() OVER(order by t0.decTime desc) as RowNumber
from (SELECT distinct q.*
from (SELECT p.SEQ_NO as seqNo,
p.LIST_NO as listNo,
p.GOODS_NO as goodsNo,
p.CODE_TS as codeTs,
to_char(p.UPDATE_TIME, 'yyyy-mm-dd HH:mm:ss') as updateTime,
to_char(p.DEC_TIME, 'yyyy-mm-dd') as decTime
FROM (SELECT *
FROM PRE_GOODS_LIST
Union All
SELECT * FROM CUR_GOODS_LIST) p
WHERE p.SEQ_NO = '99212236-1b10-4cb4-bf01-37ba7c360201'
order by updatetime desc) q) t0) t
where t.RowNumber between 1 and 15
改为
union
试试
得到的数据行是不稳定的,建议你使用一个不重复的列进行排序,或使用 decTime 和其他列联合排序,保证唯一,才能得到稳定的行;
恩。union all的两张表结构是一样的,数据本来就不会重复,不知道为啥去掉里面的distinct,分页的第一页和第二页中的数据就不会重复出现了,敢问兄台知道原因么
有可能因为它造成的。
但,只是有可能。你先试试
这两张表结构是一样的,这两张表正常的情况下是不会出现重复数据的,您看看为了显示分页,按updateTime排序,这个SQL语句最优的写法该怎么写呢?分页不重复的sql我大致知道怎么解决,但是本人对多重嵌套的SQL感觉有点乱,您刚才说的排序,我也觉得贴上的SQL有部分排序的地方可以去掉,您可以写个优化后的SQL么