描述
有一个薪水表,salaries简况如下:
建表语句如下:
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
请你查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t,以上例子输出如下:
提交代码:
select a.emp_no,count() as t from salaries a
inner join salaries b
on a.to_date = b.from_date
and a.emp_no = b.emp_no
where b.salary > a.salary
group by a.emp_no
having t > 15
用with as
with a as (select * from salaries),
b as (select * from salaries)
with inc as
(select b.emp_no,b.salary,b.from_date,b.to_date from a,b
where a.to_date = b.from_date
and a.salary < b.salary
)
select emp_no,count(salary) as t
from inc
where t > 15
都没通过,据说环境不支持
直接分组,用group by,通过
select emp_no, count() t
from salaries
group by emp_no
having t > 15;
提交结果:答案正确 运行时间:15ms 占用内存:3324KB 使用语言:Sqlite 用例通过率:100.00%
注意group by 和having的用法,with as了解一下。