https://www.nowcoder.com/share/jump/3149862161710217772662
这道牛客题的mysql,我想使用存储过程来做,如果满足第一个条件,也就是有任意一个0级用户未完成试卷数大于2,就用第一个sql查询语句,如果没有则用第二个sql语句,利用if 条件 then else then来实现用那个函数。具体代码我是这样写的
create procedure p15()
begin
if ((select any_value(sum(if(submit_time is null ,1,0))) from exam_record as ex,user_info as er where er.level=0)>2)
then
(select distinct
uid,
sum(
case
when submit_time is not null then 1
else 0
end
) as incomplete_cnt,
sum(
(
case
when submit_time is not null then 1
else 0
end
)
) / count(*) as incomplete_rate
from
exam_record
where
uid in (
select
uid
from
user_info
where
level = 0
)
group by uid)
else
(select
uid,
sum(
case
when submit_time is not null then 1
else 0
end
) as incomplete_cnt,
sum(
(
case
when submit_time is not null then 1
else 0
end
)
) / count(*) as incomplete_rate
from
exam_record group by uid) end if;
end;
但是在我的编译器上面直接是报错,我不知道为啥不能这样写。请佬解答一下