o_coby_个人页

17题 select company,avg(salary) as salary from ( select * from ( select * from ( select x.sort,x.company,x.salary,y.total_num from ( select row_number() over(partition by company order by salary) as sort, * from employee ) as x inner join (select count(1) as total_num, company from employee group by company) as y on x.company = y.company ) as z where z.total_num % 2 = 0 ) as b where b.sort = b.total_num / 2 or b.sort = b.total_num / 2 + 1 ) as c group by company union all select company,salary from ( select * from ( select x.sort,x.company,x.salary,y.total_num from ( select row_number() over(partition by company order by salary) as sort, * from employee ) as x inner join (select count(1) as total_num, company from employee group by company) as y on x.company = y.company ) as z where z.total_num % 2 = 1 ) as b where b.sort = (b.total_num + 1) / 2; or select case when total_num_of_each_company % 2 = 1 then salary else (salary + next_row_salary) / 2 end as mid_salary, * from ( select case when total_num_of_each_company % 2 = 1 then (total_num_of_each_company + 1) / 2 else total_num_of_each_company / 2 end as mid_row_num, * from ( select last_value(sort_by_salary_num) over(partition by company order by company) as total_num_of_each_company, * from( select row_number() over(partition by company order by salary) as sort_by_salary_num, lead(salary) over(partition by company order by salary) as next_row_salary, * from employee ) as x ) as y ) as z where z.mid_row_num = z.sort_by_salary_num; 18题 select product_id,year as first_year,quantity, price from ( select row_number() over(partition by product_id order by year) as sort, * from sales ) as x where sort = 1 order by product_id; 如果结果中不展示product name，那么第二张表，product可以不用，直接一张sales就可以了。 19题 select one.stuid from (select * from sc as x where x.cid = '001') as one INNER JOIN (select * from sc as x where x.cid = '002') as two on one.stuid = two.stuid where one.course > two.course; 20题 select salary as secondHighSalary from ( select dense_rank() over(partition by 1 order by salary desc) as dense_rank_sort, salary from employee_bak ) as x where x.dense_rank_sort = 2 limit 1;

看到了表名称，不一定有查询查询。