383 lines
24 KiB
SQL
383 lines
24 KiB
SQL
-- 钱包账本核对草稿（2026-10-22）；只含 SELECT 和会话变量 SET，不建表、不改数据。
|
|
-- 本文件未连接任何数据库、未执行生产核对，不能将下列查询当作已有线上结果。
|
|
-- 适用 MySQL 8 / PolarDB MySQL 8。三个库须在同一实例且核对账号有 SELECT 权限。
|
|
-- 使用前只填写本租户编号。NULL 默认不会匹配任何业务数据，避免不知租户时查全库。
|
|
-- 核对的是用户给出的“全权限、未额外筛选商户/商户类型/钱包类型”场景；
|
|
-- 若实际账号有行级权限或商户类型限制，应先将相同限制添加到新旧两侧，不能直接拿全租户结果与页面比较。
|
|
-- 日期使用中国业务时间；请确保会话中 DATETIME/TIMESTAMP 的解释与应用一致，本脚本不更改时区。
|
|
SET @recon_tenant_id = NULL;
|
|
SET @recon_from = CAST('2026-09-01 00:00:00' AS DATETIME);
|
|
SET @recon_to = CAST('2026-10-01 00:00:00' AS DATETIME);
|
|
SET @recon_excluded_dept_1 = 1875013738914156546;
|
|
SET @recon_excluded_dept_2 = 1910218102049226753;
|
|
SET @recon_excluded_dept_3 = 1910218128624336898;
|
|
SET @recon_excluded_dept_4 = 1942046316044144642;
|
|
|
|
-- 用户报告的原页面结果，单位分；用于比较是否复现，绝不代表本次已验证。
|
|
SET @recon_reported_merchant_cents = 429542126;
|
|
SET @recon_reported_department_cents = 429394976;
|
|
SET @recon_reported_merchant_count = 64361;
|
|
SET @recon_reported_department_count = 64133;
|
|
|
|
SELECT @recon_tenant_id AS selected_tenant,
|
|
@recon_from AS inclusive_start, @recon_to AS exclusive_end,
|
|
CASE WHEN @recon_tenant_id IS NULL THEN '请先填写租户编号，后续空结果不是零流水结论' ELSE '已指定租户' END AS setup_status,
|
|
@recon_reported_merchant_cents - @recon_reported_department_cents AS reported_delta_cents,
|
|
@recon_reported_merchant_count - @recon_reported_department_count AS reported_delta_count;
|
|
|
|
-- 1. 新口径原始账本基准：PAYMENT=4 价格取反；REFUND=5 保留原始符号；零金额也计次。
|
|
-- 部门排除保留 dept_id=NULL，这些交易必须进入“未归属部门/非订单未归属”桶。
|
|
WITH ledger_base AS (
|
|
SELECT ledger.*,
|
|
CASE WHEN ledger.biz_id REGEXP '^[0-9]{1,19}$'
|
|
THEN CAST(ledger.biz_id AS UNSIGNED) ELSE NULL END AS numeric_biz_id
|
|
FROM pjcloudx_wallet.wallet_transaction ledger
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND ledger.tenant_id = @recon_tenant_id
|
|
AND ledger.del_flag = '0'
|
|
AND ledger.biz_type IN ('4', '5')
|
|
AND ledger.create_time >= @recon_from
|
|
AND ledger.create_time < @recon_to
|
|
AND (ledger.dept_id IS NULL OR ledger.dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4))
|
|
)
|
|
SELECT COALESCE(-SUM(CASE WHEN biz_type = '4' THEN price ELSE 0 END), 0) AS payment_cents,
|
|
COUNT(CASE WHEN biz_type = '4' THEN 1 END) AS payment_count,
|
|
COALESCE(SUM(CASE WHEN biz_type = '5' THEN price ELSE 0 END), 0) AS refund_cents,
|
|
COUNT(CASE WHEN biz_type = '5' THEN 1 END) AS refund_count,
|
|
COALESCE(-SUM(price), 0) AS net_cents,
|
|
COUNT(CASE WHEN biz_type = '4' THEN 1 END)
|
|
- COUNT(CASE WHEN biz_type = '5' THEN 1 END) AS net_count
|
|
FROM ledger_base;
|
|
|
|
-- 2. 同一批账本按真实商户归属汇总，金额和次数仍完全来自账本。
|
|
-- 订单/售后只补商户关系，不限制其软删除、状态、支付金额及支付/创建时间。
|
|
-- 精确业务编号 + 同租户 + 同钱包，未匹配项显示“非订单/未归属”；不能漏掉这个桶。
|
|
WITH ledger_base AS (
|
|
SELECT ledger.*,
|
|
CASE WHEN ledger.biz_id REGEXP '^[0-9]{1,19}$'
|
|
THEN CAST(ledger.biz_id AS UNSIGNED) ELSE NULL END AS numeric_biz_id
|
|
FROM pjcloudx_wallet.wallet_transaction ledger
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND ledger.tenant_id = @recon_tenant_id
|
|
AND ledger.del_flag = '0'
|
|
AND ledger.biz_type IN ('4', '5')
|
|
AND ledger.create_time >= @recon_from
|
|
AND ledger.create_time < @recon_to
|
|
AND (ledger.dept_id IS NULL OR ledger.dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4))
|
|
),
|
|
merchant_ledger AS (
|
|
SELECT ledger.*, ledger_order.merchant_id
|
|
FROM ledger_base ledger
|
|
LEFT JOIN pjcloudx_hardware.hard_after_sale ledger_refund
|
|
ON ledger.biz_type = '5'
|
|
AND ledger_refund.tenant_id = ledger.tenant_id
|
|
AND ledger_refund.id = ledger.numeric_biz_id
|
|
AND CAST(ledger.biz_id AS BINARY) = CAST(CONCAT('', ledger_refund.id) AS BINARY)
|
|
LEFT JOIN pjcloudx_hardware.hard_order ledger_order
|
|
ON ledger_order.tenant_id = ledger.tenant_id
|
|
AND ledger_order.wallet_id = ledger.wallet_id
|
|
AND ledger_order.id = CASE WHEN ledger.biz_type = '4' THEN ledger.numeric_biz_id
|
|
WHEN ledger.biz_type = '5' THEN ledger_refund.order_id ELSE NULL END
|
|
AND (ledger.biz_type = '5'
|
|
OR CAST(ledger.biz_id AS BINARY) = CAST(CONCAT('', ledger_order.id) AS BINARY))
|
|
)
|
|
SELECT merchant_id,
|
|
CASE WHEN merchant_id IS NULL THEN '非订单/未归属' ELSE CONCAT('商户(', merchant_id, ')') END AS merchant_bucket,
|
|
COALESCE(-SUM(CASE WHEN biz_type = '4' THEN price ELSE 0 END), 0) AS payment_cents,
|
|
COUNT(CASE WHEN biz_type = '4' THEN 1 END) AS payment_count,
|
|
COALESCE(SUM(CASE WHEN biz_type = '5' THEN price ELSE 0 END), 0) AS refund_cents,
|
|
COUNT(CASE WHEN biz_type = '5' THEN 1 END) AS refund_count,
|
|
COALESCE(-SUM(price), 0) AS net_cents,
|
|
COUNT(CASE WHEN biz_type = '4' THEN 1 END)
|
|
- COUNT(CASE WHEN biz_type = '5' THEN 1 END) AS net_count
|
|
FROM merchant_ledger
|
|
GROUP BY merchant_id
|
|
ORDER BY merchant_id;
|
|
|
|
-- 3. 同一批账本按流水历史部门汇总；各组相加应等于第1组、第2组结果。
|
|
-- 不与当前部门列表做 INNER JOIN，物理缺失部门及 NULL 部门仍保留。
|
|
WITH ledger_base AS (
|
|
SELECT ledger.*,
|
|
CASE WHEN ledger.biz_id REGEXP '^[0-9]{1,19}$'
|
|
THEN CAST(ledger.biz_id AS UNSIGNED) ELSE NULL END AS numeric_biz_id
|
|
FROM pjcloudx_wallet.wallet_transaction ledger
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND ledger.tenant_id = @recon_tenant_id
|
|
AND ledger.del_flag = '0'
|
|
AND ledger.biz_type IN ('4', '5')
|
|
AND ledger.create_time >= @recon_from
|
|
AND ledger.create_time < @recon_to
|
|
AND (ledger.dept_id IS NULL OR ledger.dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4))
|
|
)
|
|
SELECT dept_id,
|
|
CASE WHEN dept_id IS NULL THEN '未归属部门' ELSE CONCAT('历史部门(', dept_id, ')') END AS department_bucket,
|
|
COALESCE(-SUM(CASE WHEN biz_type = '4' THEN price ELSE 0 END), 0) AS payment_cents,
|
|
COUNT(CASE WHEN biz_type = '4' THEN 1 END) AS payment_count,
|
|
COALESCE(SUM(CASE WHEN biz_type = '5' THEN price ELSE 0 END), 0) AS refund_cents,
|
|
COUNT(CASE WHEN biz_type = '5' THEN 1 END) AS refund_count,
|
|
COALESCE(-SUM(price), 0) AS net_cents,
|
|
COUNT(CASE WHEN biz_type = '4' THEN 1 END)
|
|
- COUNT(CASE WHEN biz_type = '5' THEN 1 END) AS net_count
|
|
FROM ledger_base
|
|
GROUP BY dept_id
|
|
ORDER BY dept_id;
|
|
|
|
-- 4. 旧两个支付链路的完整集合差异（仅解释旧“流水金额/次数”，不把退款混入支付差额）。
|
|
-- 旧商户：订单 pay_time BETWEEN（含右端）、订单 user_dept_id、merchant_id非空、pay_price/每单一次。
|
|
-- 旧部门：订单 create_time BETWEEN（含右端）、最新有效 PAYMENT 历史部门（不限流水时间/金额符号），
|
|
-- 无快照则回退订单部门；最后仅输出 UPMS 存在的部门。UPMS 软删除部门仍存在，不能过滤del_flag。
|
|
-- 该查询故意保留旧 BETWEEN；第1至3组的新账本使用半开区间，两者并非相同口径。
|
|
-- 本段模拟现有源码的全权限行为；若部署代码版本不同，先比对源码，不可硬解释为生产差额。
|
|
WITH order_basis AS (
|
|
SELECT a.id, a.tenant_id, a.wallet_id, a.merchant_id, a.user_dept_id,
|
|
a.pay_price, a.pay_time, a.create_time,
|
|
COALESCE((
|
|
SELECT history.dept_id
|
|
FROM pjcloudx_wallet.wallet_transaction history
|
|
WHERE history.tenant_id = a.tenant_id
|
|
AND history.biz_id = CONCAT('', a.id)
|
|
AND CAST(history.biz_id AS BINARY) = CAST(CONCAT('', a.id) AS BINARY)
|
|
AND history.wallet_id = a.wallet_id
|
|
AND history.biz_type = '4'
|
|
AND CAST(history.biz_type AS BINARY) = CAST('4' AS BINARY)
|
|
AND history.del_flag = '0'
|
|
AND history.dept_id IS NOT NULL
|
|
ORDER BY history.id DESC LIMIT 1
|
|
), a.user_dept_id) AS historical_dept_id
|
|
FROM pjcloudx_hardware.hard_order a
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND a.tenant_id = @recon_tenant_id AND a.del_flag = '0'
|
|
AND a.order_status IN ('5', '20', '10')
|
|
AND ((a.pay_time BETWEEN @recon_from AND @recon_to)
|
|
OR (a.create_time BETWEEN @recon_from AND @recon_to))
|
|
),
|
|
old_flags AS (
|
|
SELECT a.*,
|
|
CASE WHEN a.pay_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS pay_time_inside,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS create_time_inside,
|
|
CASE WHEN a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS order_department_inside,
|
|
CASE WHEN a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS historical_department_inside,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
) THEN 1 ELSE 0 END AS historical_department_exists,
|
|
CASE WHEN a.merchant_id IS NOT NULL
|
|
AND a.pay_time BETWEEN @recon_from AND @recon_to
|
|
AND a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
THEN 1 ELSE 0 END AS old_merchant_match,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to
|
|
AND a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
AND EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
)
|
|
THEN 1 ELSE 0 END AS old_department_match
|
|
FROM order_basis a
|
|
)
|
|
SELECT COALESCE(SUM(CASE WHEN old_merchant_match = 1 THEN pay_price ELSE 0 END), 0) AS old_merchant_cents,
|
|
SUM(old_merchant_match) AS old_merchant_count,
|
|
COALESCE(SUM(CASE WHEN old_department_match = 1 THEN pay_price ELSE 0 END), 0) AS old_department_cents,
|
|
SUM(old_department_match) AS old_department_count,
|
|
COALESCE(SUM(COALESCE(pay_price, 0) * (old_merchant_match - old_department_match)), 0) AS reconstructed_delta_cents,
|
|
COALESCE(SUM(old_merchant_match - old_department_match), 0) AS reconstructed_delta_count,
|
|
@recon_reported_merchant_cents - @recon_reported_department_cents AS reported_delta_cents,
|
|
@recon_reported_merchant_count - @recon_reported_department_count AS reported_delta_count,
|
|
CASE WHEN @recon_tenant_id IS NOT NULL
|
|
AND COALESCE(SUM(COALESCE(pay_price, 0) * (old_merchant_match - old_department_match)), 0)
|
|
= @recon_reported_merchant_cents - @recon_reported_department_cents
|
|
AND COALESCE(SUM(old_merchant_match - old_department_match), 0)
|
|
= @recon_reported_merchant_count - @recon_reported_department_count
|
|
THEN '金额及次数差额同时复现，仍需下组逐笔核对' ELSE '未同时复现，检查租户/权限/部署版本/元数据变化' END AS comparison_status
|
|
FROM old_flags;
|
|
|
|
-- 5. 逐笔列出旧链路集合差：正向仅进商户，负向仅进部门。
|
|
-- signed_difference_cents/count 相加应等于第4组重建差额；同时查看各时间、部门、缺失元数据列。
|
|
WITH order_basis AS (
|
|
SELECT a.id, a.tenant_id, a.wallet_id, a.merchant_id, a.user_dept_id,
|
|
a.pay_price, a.pay_time, a.create_time,
|
|
COALESCE((
|
|
SELECT history.dept_id
|
|
FROM pjcloudx_wallet.wallet_transaction history
|
|
WHERE history.tenant_id = a.tenant_id
|
|
AND history.biz_id = CONCAT('', a.id)
|
|
AND CAST(history.biz_id AS BINARY) = CAST(CONCAT('', a.id) AS BINARY)
|
|
AND history.wallet_id = a.wallet_id
|
|
AND history.biz_type = '4'
|
|
AND CAST(history.biz_type AS BINARY) = CAST('4' AS BINARY)
|
|
AND history.del_flag = '0'
|
|
AND history.dept_id IS NOT NULL
|
|
ORDER BY history.id DESC LIMIT 1
|
|
), a.user_dept_id) AS historical_dept_id
|
|
FROM pjcloudx_hardware.hard_order a
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND a.tenant_id = @recon_tenant_id AND a.del_flag = '0'
|
|
AND a.order_status IN ('5', '20', '10')
|
|
AND ((a.pay_time BETWEEN @recon_from AND @recon_to)
|
|
OR (a.create_time BETWEEN @recon_from AND @recon_to))
|
|
),
|
|
old_flags AS (
|
|
SELECT a.*,
|
|
CASE WHEN a.pay_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS pay_time_inside,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS create_time_inside,
|
|
CASE WHEN a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS order_department_inside,
|
|
CASE WHEN a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS historical_department_inside,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
) THEN 1 ELSE 0 END AS historical_department_exists,
|
|
CASE WHEN a.merchant_id IS NOT NULL
|
|
AND a.pay_time BETWEEN @recon_from AND @recon_to
|
|
AND a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
THEN 1 ELSE 0 END AS old_merchant_match,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to
|
|
AND a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
AND EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
)
|
|
THEN 1 ELSE 0 END AS old_department_match
|
|
FROM order_basis a
|
|
)
|
|
SELECT id AS order_id, merchant_id, wallet_id,
|
|
pay_time AS old_merchant_time, create_time AS old_department_time,
|
|
user_dept_id AS old_merchant_department, historical_dept_id AS old_department_department,
|
|
pay_price AS order_payment_cents,
|
|
old_merchant_match, old_department_match,
|
|
CASE WHEN old_merchant_match = 1 THEN '仅旧商户链路' ELSE '仅旧部门链路' END AS difference_direction,
|
|
COALESCE(pay_price, 0) * (old_merchant_match - old_department_match) AS signed_difference_cents,
|
|
old_merchant_match - old_department_match AS signed_difference_count,
|
|
pay_time_inside, create_time_inside, order_department_inside, historical_department_inside,
|
|
historical_department_exists
|
|
FROM old_flags
|
|
WHERE old_merchant_match <> old_department_match
|
|
ORDER BY difference_direction, id;
|
|
|
|
-- 6. 单独隔离“订单支付时间 vs 订单创建时间”差集：其他条件固定为旧商户的订单部门、真实商户及有效订单。
|
|
-- 这只是时间因素，不与第7组机械相加，时间和部门因素可能交叠。
|
|
WITH order_basis AS (
|
|
SELECT a.id, a.tenant_id, a.wallet_id, a.merchant_id, a.user_dept_id,
|
|
a.pay_price, a.pay_time, a.create_time,
|
|
COALESCE((
|
|
SELECT history.dept_id
|
|
FROM pjcloudx_wallet.wallet_transaction history
|
|
WHERE history.tenant_id = a.tenant_id
|
|
AND history.biz_id = CONCAT('', a.id)
|
|
AND CAST(history.biz_id AS BINARY) = CAST(CONCAT('', a.id) AS BINARY)
|
|
AND history.wallet_id = a.wallet_id
|
|
AND history.biz_type = '4'
|
|
AND CAST(history.biz_type AS BINARY) = CAST('4' AS BINARY)
|
|
AND history.del_flag = '0'
|
|
AND history.dept_id IS NOT NULL
|
|
ORDER BY history.id DESC LIMIT 1
|
|
), a.user_dept_id) AS historical_dept_id
|
|
FROM pjcloudx_hardware.hard_order a
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND a.tenant_id = @recon_tenant_id AND a.del_flag = '0'
|
|
AND a.order_status IN ('5', '20', '10')
|
|
AND ((a.pay_time BETWEEN @recon_from AND @recon_to)
|
|
OR (a.create_time BETWEEN @recon_from AND @recon_to))
|
|
),
|
|
old_flags AS (
|
|
SELECT a.*,
|
|
CASE WHEN a.pay_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS pay_time_inside,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS create_time_inside,
|
|
CASE WHEN a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS order_department_inside,
|
|
CASE WHEN a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS historical_department_inside,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
) THEN 1 ELSE 0 END AS historical_department_exists,
|
|
CASE WHEN a.merchant_id IS NOT NULL
|
|
AND a.pay_time BETWEEN @recon_from AND @recon_to
|
|
AND a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
THEN 1 ELSE 0 END AS old_merchant_match,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to
|
|
AND a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
AND EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
)
|
|
THEN 1 ELSE 0 END AS old_department_match
|
|
FROM order_basis a
|
|
)
|
|
SELECT CASE WHEN pay_time_inside = 1 THEN '仅支付时间命中' ELSE '仅创建时间命中' END AS time_difference,
|
|
COUNT(*) AS order_count,
|
|
COALESCE(SUM(pay_price), 0) AS order_payment_cents,
|
|
COALESCE(SUM(COALESCE(pay_price, 0) * (pay_time_inside - create_time_inside)), 0) AS signed_time_delta_cents,
|
|
SUM(pay_time_inside - create_time_inside) AS signed_time_delta_count
|
|
FROM old_flags
|
|
WHERE merchant_id IS NOT NULL AND order_department_inside = 1
|
|
AND pay_time_inside <> create_time_inside
|
|
GROUP BY pay_time_inside, create_time_inside
|
|
ORDER BY pay_time_inside DESC;
|
|
|
|
-- 7. 单独隔离“订单部门快照 vs 最新钱包支付历史部门”差集：统一使用订单支付时间、有效订单及真实商户。
|
|
-- 对照的是历史快照归属，不以当前用户部门代替；部门物理缺失的影响另看第5组 exists 字段。
|
|
-- 本组两方向均可能存在，不能因为总差额为正就只查“测试部门迁出”。
|
|
WITH order_basis AS (
|
|
SELECT a.id, a.tenant_id, a.wallet_id, a.merchant_id, a.user_dept_id,
|
|
a.pay_price, a.pay_time, a.create_time,
|
|
COALESCE((
|
|
SELECT history.dept_id
|
|
FROM pjcloudx_wallet.wallet_transaction history
|
|
WHERE history.tenant_id = a.tenant_id
|
|
AND history.biz_id = CONCAT('', a.id)
|
|
AND CAST(history.biz_id AS BINARY) = CAST(CONCAT('', a.id) AS BINARY)
|
|
AND history.wallet_id = a.wallet_id
|
|
AND history.biz_type = '4'
|
|
AND CAST(history.biz_type AS BINARY) = CAST('4' AS BINARY)
|
|
AND history.del_flag = '0'
|
|
AND history.dept_id IS NOT NULL
|
|
ORDER BY history.id DESC LIMIT 1
|
|
), a.user_dept_id) AS historical_dept_id
|
|
FROM pjcloudx_hardware.hard_order a
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND a.tenant_id = @recon_tenant_id AND a.del_flag = '0'
|
|
AND a.order_status IN ('5', '20', '10')
|
|
AND ((a.pay_time BETWEEN @recon_from AND @recon_to)
|
|
OR (a.create_time BETWEEN @recon_from AND @recon_to))
|
|
),
|
|
old_flags AS (
|
|
SELECT a.*,
|
|
CASE WHEN a.pay_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS pay_time_inside,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to THEN 1 ELSE 0 END AS create_time_inside,
|
|
CASE WHEN a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS order_department_inside,
|
|
CASE WHEN a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4) THEN 1 ELSE 0 END AS historical_department_inside,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
) THEN 1 ELSE 0 END AS historical_department_exists,
|
|
CASE WHEN a.merchant_id IS NOT NULL
|
|
AND a.pay_time BETWEEN @recon_from AND @recon_to
|
|
AND a.user_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
THEN 1 ELSE 0 END AS old_merchant_match,
|
|
CASE WHEN a.create_time BETWEEN @recon_from AND @recon_to
|
|
AND a.historical_dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4)
|
|
AND EXISTS (
|
|
SELECT 1 FROM pjcloudx.sys_dept department
|
|
WHERE department.tenant_id = a.tenant_id AND department.dept_id = a.historical_dept_id
|
|
)
|
|
THEN 1 ELSE 0 END AS old_department_match
|
|
FROM order_basis a
|
|
)
|
|
SELECT id AS order_id, pay_time, wallet_id, merchant_id,
|
|
user_dept_id AS order_department, historical_dept_id AS wallet_snapshot_department,
|
|
order_department_inside, historical_department_inside,
|
|
COALESCE(pay_price, 0) * (order_department_inside - historical_department_inside) AS signed_department_delta_cents,
|
|
order_department_inside - historical_department_inside AS signed_department_delta_count
|
|
FROM old_flags
|
|
WHERE merchant_id IS NOT NULL AND pay_time_inside = 1
|
|
AND order_department_inside <> historical_department_inside
|
|
ORDER BY signed_department_delta_count DESC, id;
|
|
|
|
-- 8. 结束时刻探针：旧 BETWEEN 会包含 10月1日00:00:00，新账本半开区间排除它。
|
|
-- 这里只展示边界交易，不把结果预设为任何金额或次数。
|
|
SELECT ledger.id AS transaction_id, ledger.biz_type, ledger.price AS signed_cents,
|
|
ledger.create_time, ledger.dept_id, ledger.wallet_id, ledger.biz_id
|
|
FROM pjcloudx_wallet.wallet_transaction ledger
|
|
WHERE @recon_tenant_id IS NOT NULL
|
|
AND ledger.tenant_id = @recon_tenant_id AND ledger.del_flag = '0'
|
|
AND ledger.biz_type IN ('4', '5') AND ledger.create_time = @recon_to
|
|
AND (ledger.dept_id IS NULL OR ledger.dept_id NOT IN (@recon_excluded_dept_1, @recon_excluded_dept_2, @recon_excluded_dept_3, @recon_excluded_dept_4))
|
|
ORDER BY ledger.id;
|