竞赛资源库

select count(distinct ci.id) as '开放竞赛总数'
from competition_infos ci
         join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id;

select count(*) as '竞赛转换成果数'
from competition_infos ci
         join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
         left join competition_resource_library crl on crl.competition_user_id = cu.id
where crl.is_transferred_to_result_library = 1;

select count(*) as '竞赛提交作品数'
from competition_infos ci
         join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id;

select count(distinct ci.id) as '已结束竞赛数'
from competition_infos ci
         join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
where ci.upload_date < now();