携程数分笔试
给我整无语了，两道SQL加起来90分了，算法就10分，关键是SQL死活过不了，麻烦各位大神帮忙看一下有什么玩意
1.
select Sno,Sname,count(distinct if(score < 60 ,Cno,null)) as failedCounts from tb_grade group by Sname,Sno having count(distinct if(score < 60 ,Cno,null)) >= 2 order by Sno desc这是最开始写的，后来改成了子查询，没用if，全通过了，也不知道这段代码问题出在哪
2.
select dept_sal.pay_month,department_id,dept_avg_sal,corp_avg_sal, case when dept_avg_sal - corp_avg_sal < 0 then "lower" when dept_avg_sal > corp_avg_sal then "higher" else "same" end as comparison from ( select b.department_id,substr(pay_date,1,7) as pay_month,round(avg(amount),1) as dept_avg_sal from salary a inner join employee b on a.employee_id = b.employee_id group by b.department_id,substr(pay_date,1,7)) dept_sal inner join ( select substr(pay_date,1,7) as pay_month,round(avg(amount),11) as corp_avg_sal from salary group by substr(pay_date,1,7)) corp_sal on dept_sal.pay_month = corp_sal.pay_month order by pay_month,department_id在本地运行结果是对的，结果怎么都过不了，好生气
腾讯开奖368人在聊
