【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.3.5.2
【问题描述】多次执行同一个sql，结果不同！！
update dw_naire_answer_rec r join (
SELECT distinct
r.q_naire_id,
r.diagnostician_id,
COUNT(
DISTINCT a.diag_obj_id,
a.q_id
) q_count
FROM
dw_actual_answer_naire_question a
JOIN dw_naire_answer_rec r ON a.naire_answer_rec_id = r.naire_answer_rec_id
JOIN dw_diag_obj o on a.diag_obj_id=o.diag_obj_id and o.diag_obj_type_id not in ('ZHIYUANJS')
WHERE
a.school_code = 'bndsftzx'
AND a.school_term_id = '2521'
GROUP BY
r.q_naire_id,
r.diagnostician_id
) a on r.q_naire_id=a.q_naire_id and r.diagnostician_id=a.diagnostician_id
set r.q_count = a.q_count
select * from dw_naire_answer_rec r join (
SELECT
r.q_naire_id,
r.diagnostician_id,
COUNT(
DISTINCT a.diag_obj_id,
a.q_id
) q_count
FROM
dw_actual_answer_naire_question a
JOIN dw_naire_answer_rec r ON a.naire_answer_rec_id = r.naire_answer_rec_id
JOIN dw_diag_obj o on a.diag_obj_id=o.diag_obj_id and o.diag_obj_type_id not in ('ZHIYUANJS')
WHERE
a.school_code = 'bndsftzx'
AND a.school_term_id = '2521'
GROUP BY
r.q_naire_id,
r.diagnostician_id
) a on r.q_naire_id=a.q_naire_id and r.diagnostician_id=a.diagnostician_id
and r.q_count = a.q_count
update dw_naire_answer_rec rr join (
SELECT distinct
r.q_naire_id,
r.diagnostician_id,
COUNT(
DISTINCT a.diag_obj_id,
a.q_id
) q_count
FROM
dw_actual_answer_naire_question a
JOIN dw_naire_answer_rec r ON a.naire_answer_rec_id = r.naire_answer_rec_id
JOIN dw_diag_obj o on a.diag_obj_id=o.diag_obj_id and o.diag_obj_type_id not in ('ZHIYUANJS')
WHERE
a.school_code = 'bndsftzx'
AND a.school_term_id = '2521'
GROUP BY
r.q_naire_id,
r.diagnostician_id
) a on rr.q_naire_id=a.q_naire_id and rr.diagnostician_id=a.diagnostician_id
set rr.q_count = a.q_count
UPDATE dw_naire_answer_rec AS r
JOIN (
SELECT
r2.q_naire_id,
r2.diagnostician_id,
COUNT(DISTINCT CONCAT(a.diag_obj_id, ‘-’, a.q_id)) AS q_count
FROM dw_actual_answer_naire_question a
JOIN dw_naire_answer_rec r2
ON a.naire_answer_rec_id = r2.naire_answer_rec_id
JOIN dw_diag_obj o
ON a.diag_obj_id = o.diag_obj_id
AND o.diag_obj_type_id NOT IN (‘ZHIYUANJS’)
WHERE a.school_code = ‘bndsftzx’
AND a.school_term_id = ‘2521’
GROUP BY r2.q_naire_id, r2.diagnostician_id
) AS t
ON r.q_naire_id = t.q_naire_id
AND r.diagnostician_id = t.diagnostician_id
SET r.q_count = t.q_count;
