mysql 写法_MYSQL 写法

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

--work1:

SELECT e.emp_no,

CONCAT(last_name, " ", first_name) AS name,

t3.title,

dp.dept_name,

t6.salary,

t7.dept_no

FROM employees e

LEFT JOIN dept_manager d

ON e.emp_no = d.emp_no

LEFT JOIN (SELECT t1.emp_no, t1.title

FROM titles t1,

(SELECT emp_no, MAX(from_date) AS from_date

FROM titles

GROUP BY emp_no) t2

WHERE t1.emp_no = t2.emp_no

AND t1.from_date = t2.from_date) t3

on t3.emp_no = e.emp_no

LEFT JOIN (SELECT t1.emp_no, t1.salary

FROM salaries t1,

(SELECT emp_no, MAX(from_date) AS from_date

FROM salaries

GROUP BY emp_no) t4

WHERE t1.emp_no = t4.emp_no

AND t1.from_date = t4.from_date) t6

on t6.emp_no = e.emp_no

LEFT JOIN (select t1.emp_no, t1.dept_no

from dept_emp t1,

(select emp_no, max(from_date) as from_date

from dept_emp

group by emp_no) t2

where t1.emp_no = t2.emp_no

and t1.from_date = t2.from_date) t7

on t7.emp_no = e.emp_no

-- dept_emp de ON de.emp_no = e.emp_no

LEFT JOIN departments dp

ON dp.dept_no = t7.dept_no

WHERE d.emp_no IS NULL

and e.emp_no = 10010;

--work2:

select tt.id,

tt.score,

(select count(1) + 1

from (select id, sum(score) score from rank group by id) a

where a.score > tt.score) as rank_no

from (select id, sum(score) score from rank group by id) tt

order by score desc;