mysql查询出现查询条件影响查询字段值的诡异问题求解

Question

问题遇到的现象和发生背景

一段由多个表组成的查询结果，根据若干个字段判断当前数据的状态：

当不增加任何条件的时候查出来一条是“已收齐”，看到部门名称是“业支领导”：

当添加筛选条件，要搜索状态为“已收齐”当数据的时候，查到的数据的确是原来“业支领导”，但是状态却变成了“未开始”，这是为什么，why？命名group_id还是存在的啊！

问题相关代码，请勿粘贴截图

原始代码：

select * from (
        select 
        case when group_checked_je>=je then '已完成'
        when checked_cnt>0 then '已完成'
        when a11.group_id is null  then '未开始'
        when a11.je_gets<je then '未收齐'
        when a11.je_gets>=je then '已收齐'
        end group_status,
        case when  a11.group_id is not null then a11.group_id else concat(a11.cycle,a11.dept_id) end id,
a11.cycle,a11.dept_id,a11.parent_id,a11.dept_name,a11.dept_long_name,a11.group_id,
a11.manager,a11.do_time_begin,a11.do_time_end,a11.do_times,
a11.cnt_dept,
a11.cnt,
        a11.je,
a11.cnt_gets,
        a11.je_gets,
a11.left_full,
a11.left_poor,
a11.remain,
a11.group_checked_je,
eff_get_fp_cnt,all_get_fp_cnt,
a11.can_gets_cnt,
checked_cnt
        from
(
        select a10.cycle,a10.dept_id,a10.parent_id,a10.dept_name,a10.dept_long_name,a10.group_id,
a10.manager,a10.do_time_begin,a10.do_time_end,a10.do_times,
        count(0) cnt_dept,
        sum(case when a10.bxed0>0 then 1 else 0 end) cnt,
        sum(a10.bxed0) je,
        sum(case when a10.bxed0>0 and  a10.je_need=0 then 1 else 0 end) cnt_gets,
        sum(user_je_eff)  je_gets,
        sum(case when a10.bxed0>0 and a10.je_need >0 and a10.user_left_fp_je>= a10.je_need then 1 else 0 end) left_full,
        sum(case when a10.bxed0>0 and a10.je_need >0 and a10.user_left_fp_je< a10.je_need then 1 else 0 end) left_poor,
        sum(case when a10.je_need>0 then 1 else 0 end) remain,
        sum(a10.user_checked_je) group_checked_je,
        sum(a10.eff_get_fp_cnt) eff_get_fp_cnt,sum(a10.all_get_fp_cnt) all_get_fp_cnt,
        sum(a10.checked_cnt) checked_cnt,
        sum(a10.can_gets_cnt) can_gets_cnt
        from
(
        select a9.cycle,a9.dept_id,a9.parent_id,a9.dept_name,a9.dept_long_name,a9.user_id,a9.login_name,
        a9.user_name, a9.bxed,a9.bxed0,a9.group_id,a9.manager,a9.do_time_begin,a9.do_time_end,a9.do_times,
a9.user_je_eff,
a9.user_je_exp,
a9.user_checked_je,
a9.eff_get_fp_cnt,
a9.all_get_fp_cnt,
a9.checked_cnt,
        ifnull( sum(b9.jshjxx) ,0) user_left_fp_je,
        case when sum(b9.jshjxx) =null then 0 else count(b9.id) end user_left_fp_cnt,
        case when a9.bxed0<=0 then 0
        when a9.bxed0>0 and a9.user_je_eff>= a9.bxed0 then 0
        else a9.bxed0-a9.user_je_eff end je_need,
        case when (case when a9.bxed0 <=0 then 0
        when a9.bxed0>0 and a9.user_je_eff>= a9.bxed0 then 0
        else a9.bxed0-a9.user_je_eff end)>0
        and (ifnull( sum(b9.jshjxx) ,0))>0    then 1 else 0 end can_gets_cnt
        from
(
        select a8.cycle,a8.dept_id,a8.parent_id,a8.dept_name,a8.dept_long_name,a8.user_id,a8.login_name,
        a8.user_name, a8.bxed,a8.group_id,a8.manager,a8.do_time_begin,a8.do_time_end,a8.do_times,a8.bxed0,
        sum(case when a8.check_status <> '-1' then a8.jshjxx else 0 end) user_je_eff,
        sum(case when a8.check_status = '-1' then a8.jshjxx else 0 end) user_je_exp,
        sum(case when a8.check_status='1' then  a8.jshjxx else 0 end) user_checked_je,
        sum(case when a8.check_status <> '-1' then 1 else 0 end) eff_get_fp_cnt,
        sum(case when a8.check_status is not null then 1 else 0 end) all_get_fp_cnt,
        sum(case when a8.check_status is not null and check_status='1' then 1 else 0 end) checked_cnt
        from
(
        select a7.cycle,a7.dept_id,a7.parent_id,a7.dept_name,a7.dept_long_name,a7.user_id,a7.login_name,
        a7.user_name, a7.bxed,a7.group_id,a7.manager,a7.do_time_begin,a7.do_time_end,a7.do_times
        ,a7.inv_id,a7.check_status,b7.jshjxx,a7.bxed0 from
(
        select a6.cycle,a6.dept_id,a6.parent_id,a6.dept_name,a6.dept_long_name,a6.user_id,a6.login_name,
        a6.user_name, a6.bxed,a6.group_id,a6.manager,a6.do_time_begin,a6.do_time_end,a6.do_times
        ,b6.inv_id,b6.status check_status,a6.bxed0 from
(
        select a5.cycle,a5.dept_id,a5.parent_id,a5.dept_name,a5.dept_long_name,a5.user_id,a5.login_name,
        a5.user_name, a5.bxed,
        case when b5.bxed0 is null then a5.bxed when b5.bxed0=0 then 0 else a5.bxed end bxed0,
        a5.group_id,a5.manager,a5.do_time_begin,a5.do_time_end,a5.do_times
        ,b5.id igu_id from
(
        select a4.cycle,a4.dept_id,a4.parent_id,a4.dept_name,a4.dept_long_name,a4.user_id,a4.login_name,
        a4.user_name, a4.bxed,b4.id group_id,b4.manager,b4.do_time_begin,b4.do_time_end,b4.do_times
        from
(
        select a3.cycle,a3.dept_id,a3.parent_id,a3.dept_name,a3.dept_long_name,a3.user_id,a3.login_name,a3.user_name,sum(a3.bxed) bxed from
(
        select a2.cycle,a2.dept_id,a2.parent_id,a2.dept_name,a2.dept_long_name,a2.user_id,a2.login_name,a2.user_name,b2.bxed from
(
        select a1.cycle,a1.dept_id,a1.parent_id,a1.dept_name,a1.dept_long_name,a1.user_id,a1.login_name,a1.user_name,b1.post_id from
(
        select '2022-01' cycle,a.dept_id,a.parent_id,a.dept_name,a.leader dept_long_name,b.user_id,b.login_name,b.user_name
        from sys_dept  a
        left join sys_user as b
        on a.dept_id=b.dept_id
        where  a.status='0'
        and b.status='0'
        and a.del_flag='0'
        and b.del_flag = '0'
        and a.parent_id=101
        ) a1 left join sys_user_post b1
        on a1.user_id=b1.user_id
        )a2  left join sys_post b2
        on a2.post_id=b2.post_id
        where b2.status='0'
        ) a3
        group by a3.cycle,a3.dept_id,a3.dept_name,a3.dept_long_name,a3.user_id,a3.login_name,a3.user_name
        )a4 left join invoice_gets_group b4
        on a4.cycle=b4.cycle
        and a4.dept_id=b4.dept_id
        ) a5 left join invoice_gets_user b5
        on a5.group_id =b5.group_id
        and a5.login_name =b5.login_name
        )a6 left join invoice_gets_user_list b6
        on a6.igu_id= b6.igu_id
        )a7 left join invoice b7
        on a7.inv_id=b7.id
)a8
        group by a8.cycle,a8.dept_id,a8.parent_id,a8.dept_name,a8.dept_long_name,a8.user_id,a8.login_name,
        a8.user_name, a8.bxed,a8.group_id,a8.manager,a8.do_time_begin,a8.do_time_end,a8.do_times,a8.bxed0
        )a9 left join invoice b9
        on a9.login_name=b9.user_name
        and b9.status = '1'
        and b9.kprq like concat(substr('2022-01',1,4),'%')
        group by a9.cycle,a9.dept_id,a9.parent_id,a9.dept_name,a9.dept_long_name,a9.user_id,a9.login_name,
        a9.user_name, a9.bxed,a9.bxed0,a9.group_id,a9.manager,a9.do_time_begin,a9.do_time_end,a9.do_times,
a9.user_je_eff,
a9.user_je_exp
)a10
        group by a10.cycle,a10.dept_id,a10.parent_id,a10.dept_name,a10.dept_long_name,a10.group_id,
a10.manager,a10.do_time_begin,a10.do_time_end,a10.do_times
)a11) a12
        where a12.group_status='已收齐'
 

运行结果及报错内容

我的解答思路和尝试过的方法

我想要达到的结果

我就想知道查出来后为什么结果中字段值变了？

Answer 1

不知原因，但是这么写是能去到，感谢老同学HZG：