Java查TiDB中的数据,排序分页,出现当前页有上一页记录。请问有哪些解决方法?

Java查TiDB中的数据,排序分页,分页数据重复出现。请问有哪些解决方法?

例如:
假设分页1:
SELECT
dmc.id AS dailyMerchantCollectId,
m.CODE AS merchantCode,
dmc.daily_date AS dailyDate,
dmc.merchant_name AS merchantName,
dmc.institution_name AS institutionName,
dmc.institution_id AS institutionId,
dmc.agent_name AS agentName,
CASE WHEN ( dmc.paid_count IS NULL ) THEN 0 ELSE dmc.paid_count END AS paidCount,
CASE WHEN ( dmc.paid_amount IS NULL ) THEN 0 ELSE dmc.paid_amount END AS paidAmount,
CASE WHEN ( dmc.refund_count IS NULL ) THEN 0 ELSE dmc.refund_count END AS refundCount,
CASE WHEN ( dmc.refund_amount IS NULL ) THEN 0 ELSE dmc.refund_amount END AS refundAmount,
CASE WHEN ( dmc.settle_count IS NULL ) THEN 0 ELSE dmc.settle_count END AS settleCount,
CASE WHEN ( dmc.settle_amount IS NULL ) THEN 0 ELSE dmc.settle_amount END AS settleAmount,
CASE WHEN ( dmc.pre_settle_amount IS NULL ) THEN 0 ELSE dmc.pre_settle_amount END AS preSettleAmount,
CASE WHEN ( dmc.rate IS NULL ) THEN ‘-’ ELSE dmc.rate END AS rate,
CASE WHEN ( dmc.rate_amount IS NULL ) THEN 0 ELSE dmc.rate_amount END AS rateAmount,
CASE WHEN ( dmc.pre_rate_amount IS NULL ) THEN 0 ELSE dmc.pre_rate_amount END AS preRateAmount,
m.sales_man AS salesMan,
m.custom_manager AS customManager,
m.export_remark AS exportRemark
FROM daily_merchant_collect AS dmc
LEFT JOIN ( SELECT id, sales_man, custom_manager, CODE, export_remark FROM merchant ) AS m ON dmc.merchant_id = m.id
WHERE dmc.daily_date BETWEEN ‘2020-10-19 00:00:00’ AND ‘2020-10-20 23:59:59’
AND dmc.institution_id = 3
GROUP BY dmc.id
ORDER BY dmc.daily_date DESC
LIMIT 30,10

假设分页2:

SELECT
dmc.id AS dailyMerchantCollectId,
m.CODE AS merchantCode,
dmc.daily_date AS dailyDate,
dmc.merchant_name AS merchantName,
dmc.institution_name AS institutionName,
dmc.institution_id AS institutionId,
dmc.agent_name AS agentName,
CASE WHEN ( dmc.paid_count IS NULL ) THEN 0 ELSE dmc.paid_count END AS paidCount,
CASE WHEN ( dmc.paid_amount IS NULL ) THEN 0 ELSE dmc.paid_amount END AS paidAmount,
CASE WHEN ( dmc.refund_count IS NULL ) THEN 0 ELSE dmc.refund_count END AS refundCount,
CASE WHEN ( dmc.refund_amount IS NULL ) THEN 0 ELSE dmc.refund_amount END AS refundAmount,
CASE WHEN ( dmc.settle_count IS NULL ) THEN 0 ELSE dmc.settle_count END AS settleCount,
CASE WHEN ( dmc.settle_amount IS NULL ) THEN 0 ELSE dmc.settle_amount END AS settleAmount,
CASE WHEN ( dmc.pre_settle_amount IS NULL ) THEN 0 ELSE dmc.pre_settle_amount END AS preSettleAmount,
CASE WHEN ( dmc.rate IS NULL ) THEN ‘-’ ELSE dmc.rate END AS rate,
CASE WHEN ( dmc.rate_amount IS NULL ) THEN 0 ELSE dmc.rate_amount END AS rateAmount,
CASE WHEN ( dmc.pre_rate_amount IS NULL ) THEN 0 ELSE dmc.pre_rate_amount END AS preRateAmount,
m.sales_man AS salesMan,
m.custom_manager AS customManager,
m.export_remark AS exportRemark
FROM daily_merchant_collect AS dmc
LEFT JOIN ( SELECT id, sales_man, custom_manager, CODE, export_remark FROM merchant ) AS m ON dmc.merchant_id = m.id
WHERE dmc.daily_date BETWEEN ‘2020-10-19 00:00:00’ AND ‘2020-10-20 23:59:59’
AND dmc.institution_id = 3
GROUP BY dmc.id
ORDER BY dmc.daily_date DESC
LIMIT 300,10

出现问题:分页2中含有分页1中的记录。请问趟过坑的前辈,都怎么解决的?

1 个赞

可以参考下这个文档,看下能否对你有所帮助:
https://book.tidb.io/session4/chapter6/page-inaction.html

1 个赞

非常感谢!

1 个赞

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。