问题遇到的现象和发生背景
一段由多个表组成的查询结果,根据若干个字段判断当前数据的状态:
当不增加任何条件的时候查出来一条是“已收齐”,看到部门名称是“业支领导”:
当添加筛选条件,要搜索状态为“已收齐”当数据的时候,查到的数据的确是原来“业支领导”,但是状态却变成了“未开始”,这是为什么,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='已收齐'
运行结果及报错内容
我的解答思路和尝试过的方法
我想要达到的结果
我就想知道查出来后为什么结果中字段值变了?