携程数分笔试第二道sql
SELECT t2.*,t1.corp_avg_sal, (CASE WHEN dept_avg_sal>corp_avg_sal THEN 'higher' WHEN dept_avg_sal<corp_avg_sal THEN 'lower' ELSE 'same' END) AS comparison FROM (SELECT DATE_FORMAT(pay_date,'%Y-%m') AS pay_month,department_id,ROUND(SUM(amount)/COUNT(*),1) AS dept_avg_sal FROM (SELECT s.employee_id,s.amount,s.pay_date,e.department_id FROM salary s LEFT JOIN employee e ON s.employee_id=e.employee_id)tmp GROUP BY pay_month,department_id)t2 LEFT JOIN (SELECT DATE_FORMAT(pay_date,'%Y-%m') AS pay_month,ROUND(SUM(amount)/COUNT(*),11) AS corp_avg_sal FROM (SELECT s.employee_id,s.amount,s.pay_date,e.department_id FROM salary s LEFT JOIN employee e ON s.employee_id=e.employee_id)tmp GROUP BY pay_month)t1 ON t2.pay_month=t1.pay_month ORDER BY pay_month,department_id ASC
家人们,帮忙看看为啥round写的保留11位,却只能保留9位小数后面全是0
我的结果
原题结果