论坛改版后，原来的账号已经丢了，补充一个另外的实际案例：
问题描述
客户写了一个复杂查询的sql反馈说分页查询的数据不对，去掉分页就正常了，具体问题描述如下：
不带条件的结果数量有1900多条，再筛选符合条件的结果应该只有10条左右。按照1000条进行分页，最多只有两页，且预期两页的数据条数加起来是10条，结果第一页的结果查询出来6条，第二页查询出来只有5条，且5条都和第一页的数据重复了。
解决方案
将order by挪到外面limit同一层后，查询就正常了
原sql如下：
SELECT
*
from
(
SELECT
*
FROM
(
WITH
rule_mat_code AS (
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_batch_pass_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_four_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_pass_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_result_value_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_standard_deviation_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
)
SELECT
t.*,
i.sort_index AS limsSortIndex,
i.belong_type AS limsBelongType,
ROUND(t.avg, 5) AS avgResult,
CONCAT (
CASE
WHEN CAST(t.minResult AS STRING) LIKE '%.%' THEN CASE
WHEN RTRIM (RTRIM (CAST(t.minResult AS STRING), '0'), '.') = '' THEN '0'
ELSE RTRIM (RTRIM (CAST(t.minResult AS STRING), '0'), '.')
END
ELSE CAST(t.minResult AS STRING)
END,
'~',
CASE
WHEN CAST(t.maxResult AS STRING) LIKE '%.%' THEN CASE
WHEN RTRIM (RTRIM (CAST(t.maxResult AS STRING), '0'), '.') = '' THEN '0'
ELSE RTRIM (RTRIM (CAST(t.maxResult AS STRING), '0'), '.')
END
ELSE CAST(t.maxResult AS STRING)
END
) AS resultRange
FROM
(
SELECT
d.insp_entrust_no AS inspEntrustNo,
COALESCE(a.supplier_code, y.SUPPLIER_CODE, m.SUPPLIER_CODE) AS supplierCode,
COALESCE(a.supplier_name, y.SUPPLIER_NAME, m.SUPPLIER_NAME) AS supplierName,
COALESCE(a.mat_code, y.MATERIAL_CODE, m.MATERIAL_CODE) AS matCode,
COALESCE(a.mat_name, y.MATERIAL_NAME, m.MATERIAL_NAME) AS matName,
CONCAT (
COALESCE(a.supplier_code, y.SUPPLIER_CODE, m.SUPPLIER_CODE),
COALESCE(a.mat_code, y.MATERIAL_CODE, m.MATERIAL_CODE)
) AS supplierMatCode,
CONCAT (
d.insp_entrust_no,
COALESCE(a.transport_type, '皮带')
) AS id,
e.result,
e.judge_mark AS judgeMark,
ROUND(
SUM(
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
)
),
2
) AS netWgt,
COALESCE(a.transport_type, '皮带') AS transportType,
e.item_code AS `code`,
e.item_name AS `name`,
e.belong_type,
e.sort_index,
g.decimal_digit AS decimal_digit,
ROUND(
CASE
WHEN SUM(
IF (
e.result IS NOT NULL
AND e.result != '',
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
) = 0 THEN NULL
ELSE SUM(
IF (
e.result IS NOT NULL
AND e.result != '',
CAST(e.result AS DECIMAL(12, 4)) * COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
) / SUM(
IF (
e.result IS NOT NULL
AND e.result != '',
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
)
END,
5
) AS avg,
MIN(
IF (
e.result = '',
NULL,
CAST(e.result AS DECIMAL(12, 4))
)
) AS minResult,
MAX(
IF (
e.result = '',
NULL,
CAST(e.result AS DECIMAL(12, 4))
)
) AS maxResult,
COUNT(IF (e.judge_mark = '3', e.result, NULL)) AS noPassCount,
GROUP_CONCAT (
IF (
e.judge_mark = '3',
CAST(e.result AS STRING),
NULL
)
) AS noPassResult,
ROUND(
SUM(
IF (
e.result IS NOT NULL
AND e.result != ''
AND e.judge_mark IN ('1', '2'),
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
) * 100 / NULLIF(
SUM(
IF (
e.judge_mark IN ('1', '2', '3'),
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
),
0
),
2
) AS passRate
FROM
ods_prod.ods_szl01_crude_fuel_inspect_info_biz d
LEFT JOIN ods_prod.ods_szl01_crude_fuel_inspect_child_info_biz e ON d.id = e.main_id
AND e.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_approach_batch_info_biz a ON a.batch_code = d.batch_code
AND a.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_ylts y ON y.WEIGHTNO = d.batch_code
LEFT JOIN ods_prod.ods_szl01_ylmt m ON m.WEIGHTNO = d.batch_code
LEFT JOIN ods_prod.ods_szl01_approach_batch_info_child_biz b ON a.id = b.main_id
AND b.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_meterage_car_weight_info c ON b.measure_num = c.waybill_code
AND c.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_inspect_item_configure_biz f ON d.mat_code = f.mat_code
AND f.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_inspect_item_configure_child_biz g ON f.id = g.main_id
AND e.item_code = g.item_code
AND g.is_deleted = 0
LEFT JOIN rule_mat_code r ON r.mat_code = d.mat_code
AND r.item_code = e.item_code
WHERE
d.is_deleted = 0
AND d.entrusted_type = '原料委托'
AND d.entrusted_status_code = 'ZT95'
AND FIND_IN_SET (
COALESCE(a.mat_name, y.MATERIAL_NAME, m.MATERIAL_NAME),
'焦炭（二级干焦)'
) > 0
AND d.batch_end_time >= '2025/09/01'
AND d.batch_end_time <= adddate ('2025/09/30', 1)
GROUP BY
d.insp_entrust_no,
COALESCE(a.supplier_code, y.SUPPLIER_CODE, m.SUPPLIER_CODE),
COALESCE(a.supplier_name, y.SUPPLIER_NAME, m.SUPPLIER_NAME),
COALESCE(a.mat_code, y.MATERIAL_CODE, m.MATERIAL_CODE),
COALESCE(a.mat_name, y.MATERIAL_NAME, m.MATERIAL_NAME),
COALESCE(a.transport_type, '皮带'),
e.item_code,
e.item_name,
e.result,
e.judge_mark,
e.belong_type,
e.sort_index,
g.decimal_digit
) t
INNER JOIN ods_prod.ods_szl01_inspect_item_configure_biz h ON t.matCode = h.mat_code
AND h.is_deleted = 0
INNER JOIN ods_prod.ods_szl01_inspect_item_configure_child_biz i ON h.id = i.main_id
AND t.`code` = i.item_code
AND i.is_deleted = 0
ORDER BY
t.id,
t.belong_type IS NULL,
t.belong_type ASC,
t.sort_index IS NULL,
t.sort_index ASC,
t.`code` ASC
) t0
LIMIT
0, 1000
) t1
where
inspEntrustNo = 'Y2509010057_P'
修复后的sql
SELECT
*
from
(
SELECT
*
FROM
(
WITH
rule_mat_code AS (
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_batch_pass_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_four_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_pass_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_result_value_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
UNION
SELECT
t1.item_name,
t1.item_code,
t1.mat_code
FROM
ods_prod.ods_szl01_supplier_score_rule_manage_standard_deviation_biz t1
WHERE
t1.is_deleted = 0
AND t1.item_code IS NOT NULL
)
SELECT
t.*,
i.sort_index AS limsSortIndex,
i.belong_type AS limsBelongType,
ROUND(t.avg, 5) AS avgResult,
CONCAT (
CASE
WHEN CAST(t.minResult AS STRING) LIKE '%.%' THEN CASE
WHEN RTRIM (RTRIM (CAST(t.minResult AS STRING), '0'), '.') = '' THEN '0'
ELSE RTRIM (RTRIM (CAST(t.minResult AS STRING), '0'), '.')
END
ELSE CAST(t.minResult AS STRING)
END,
'~',
CASE
WHEN CAST(t.maxResult AS STRING) LIKE '%.%' THEN CASE
WHEN RTRIM (RTRIM (CAST(t.maxResult AS STRING), '0'), '.') = '' THEN '0'
ELSE RTRIM (RTRIM (CAST(t.maxResult AS STRING), '0'), '.')
END
ELSE CAST(t.maxResult AS STRING)
END
) AS resultRange
FROM
(
SELECT
d.insp_entrust_no AS inspEntrustNo,
COALESCE(a.supplier_code, y.SUPPLIER_CODE, m.SUPPLIER_CODE) AS supplierCode,
COALESCE(a.supplier_name, y.SUPPLIER_NAME, m.SUPPLIER_NAME) AS supplierName,
COALESCE(a.mat_code, y.MATERIAL_CODE, m.MATERIAL_CODE) AS matCode,
COALESCE(a.mat_name, y.MATERIAL_NAME, m.MATERIAL_NAME) AS matName,
CONCAT (
COALESCE(a.supplier_code, y.SUPPLIER_CODE, m.SUPPLIER_CODE),
COALESCE(a.mat_code, y.MATERIAL_CODE, m.MATERIAL_CODE)
) AS supplierMatCode,
CONCAT (
d.insp_entrust_no,
COALESCE(a.transport_type, '皮带')
) AS id,
e.result,
e.judge_mark AS judgeMark,
ROUND(
SUM(
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
)
),
2
) AS netWgt,
COALESCE(a.transport_type, '皮带') AS transportType,
e.item_code AS `code`,
e.item_name AS `name`,
e.belong_type,
e.sort_index,
g.decimal_digit AS decimal_digit,
ROUND(
CASE
WHEN SUM(
IF (
e.result IS NOT NULL
AND e.result != '',
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
) = 0 THEN NULL
ELSE SUM(
IF (
e.result IS NOT NULL
AND e.result != '',
CAST(e.result AS DECIMAL(12, 4)) * COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
) / SUM(
IF (
e.result IS NOT NULL
AND e.result != '',
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
)
END,
5
) AS avg,
MIN(
IF (
e.result = '',
NULL,
CAST(e.result AS DECIMAL(12, 4))
)
) AS minResult,
MAX(
IF (
e.result = '',
NULL,
CAST(e.result AS DECIMAL(12, 4))
)
) AS maxResult,
COUNT(IF (e.judge_mark = '3', e.result, NULL)) AS noPassCount,
GROUP_CONCAT (
IF (
e.judge_mark = '3',
CAST(e.result AS STRING),
NULL
)
) AS noPassResult,
ROUND(
SUM(
IF (
e.result IS NOT NULL
AND e.result != ''
AND e.judge_mark IN ('1', '2'),
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
) * 100 / NULLIF(
SUM(
IF (
e.judge_mark IN ('1', '2', '3'),
COALESCE(
c.net_wgt,
CAST(y.INTONNES AS DOUBLE),
CAST(m.INTONNES AS DOUBLE)
),
0
)
),
0
),
2
) AS passRate
FROM
ods_prod.ods_szl01_crude_fuel_inspect_info_biz d
LEFT JOIN ods_prod.ods_szl01_crude_fuel_inspect_child_info_biz e ON d.id = e.main_id
AND e.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_approach_batch_info_biz a ON a.batch_code = d.batch_code
AND a.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_ylts y ON y.WEIGHTNO = d.batch_code
LEFT JOIN ods_prod.ods_szl01_ylmt m ON m.WEIGHTNO = d.batch_code
LEFT JOIN ods_prod.ods_szl01_approach_batch_info_child_biz b ON a.id = b.main_id
AND b.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_meterage_car_weight_info c ON b.measure_num = c.waybill_code
AND c.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_inspect_item_configure_biz f ON d.mat_code = f.mat_code
AND f.is_deleted = 0
LEFT JOIN ods_prod.ods_szl01_inspect_item_configure_child_biz g ON f.id = g.main_id
AND e.item_code = g.item_code
AND g.is_deleted = 0
LEFT JOIN rule_mat_code r ON r.mat_code = d.mat_code
AND r.item_code = e.item_code
WHERE
d.is_deleted = 0
AND d.entrusted_type = '原料委托'
AND d.entrusted_status_code = 'ZT95'
AND FIND_IN_SET (
COALESCE(a.mat_name, y.MATERIAL_NAME, m.MATERIAL_NAME),
'焦炭（二级干焦)'
) > 0
AND d.batch_end_time >= '2025/09/01'
AND d.batch_end_time <= adddate ('2025/09/30', 1)
GROUP BY
d.insp_entrust_no,
COALESCE(a.supplier_code, y.SUPPLIER_CODE, m.SUPPLIER_CODE),
COALESCE(a.supplier_name, y.SUPPLIER_NAME, m.SUPPLIER_NAME),
COALESCE(a.mat_code, y.MATERIAL_CODE, m.MATERIAL_CODE),
COALESCE(a.mat_name, y.MATERIAL_NAME, m.MATERIAL_NAME),
COALESCE(a.transport_type, '皮带'),
e.item_code,
e.item_name,
e.result,
e.judge_mark,
e.belong_type,
e.sort_index,
g.decimal_digit
) t
INNER JOIN ods_prod.ods_szl01_inspect_item_configure_biz h ON t.matCode = h.mat_code
AND h.is_deleted = 0
INNER JOIN ods_prod.ods_szl01_inspect_item_configure_child_biz i ON h.id = i.main_id
AND t.`code` = i.item_code
AND i.is_deleted = 0
) t0
ORDER BY
id,
belong_type IS NULL,
belong_type ASC,
sort_index IS NULL,
sort_index ASC,
`code` ASC
LIMIT
1000, 1000
) t1
where
inspEntrustNo = 'Y2509010057_P'
