Loading

EAS查询分析器通过SQL分页查询数据

-- 通过分页查询不在费用报销单中的商务卡流水数据
select * from (select t.*, row_number() over (order by t.CFSerialNumber) rowno from (
-- 这里查询出来的是不在费用/付款报销单中的商务卡流水总数量,如果需要商务卡流水ID/序列号分别将第一行的 COUNT(*) 换成 t1.FId/t1.CFSerialNumber
SELECT t1.CFSerialNumber FROM CT_BC_BUSINESSCARDFLOW t1 WHERE t1.CFSerialNumber NOT IN (
WITH t(sub, str) AS (
    SELECT substr(CFBUSINESSCARDFLOW, 1, instr(CFBUSINESSCARDFLOW, ';') - 1), substr(concat(CFBUSINESSCARDFLOW,';'), instr(CFBUSINESSCARDFLOW, ';') + 1) 
    FROM T_BC_BIZACCOUNTBILLENTRY ENTRY
    LEFT JOIN T_BC_BizAccountBill BILL ON ENTRY.FBILLID = BILL.FID
    -- 单据分录商务卡流水信息不为空
    WHERE ENTRY.CFBusinessCardFlow IS NOT NULL
    -- 单据状态(不是暂存/废弃/取消)
    AND BILL.FSTATE <> 20
    AND BILL.FSTATE <> 27
    AND BILL.FSTATE <> 50
    
    UNION ALL
    
    SELECT substr(str, 1, instr(str, ';') - 1), substr(str, instr(str, ';') + 1)
    FROM t WHERE instr(str, ';') > 0
) 
SELECT sub
FROM t
WHERE sub is not null
)
-- 交易日期(今年)
AND CFTRANSDATE >= {ts'2022-01-01'}
AND CFTRANSDATE <= {ts'2022-12-31'}
) t 
-- 查询数量(查询分析器每页最多显示6500条,可自己更改)
) where rowno > 6500 
and rowno < 13001
posted @ 2023-01-28 11:39  Schieber  阅读(19)  评论(0编辑  收藏  举报