dept_emp部门-员工表:
以及salaries工资表:
现在统计各个部门的工资条数,按照dept_no、dept_name、sum(工资条数)输出,并按dept_no升序排列会得到如下结果:
所以下列MySQL语句正确的是:
dept_emp部门-员工表:
以及salaries工资表:
现在统计各个部门的工资条数,按照dept_no、dept_name、sum(工资条数)输出,并按dept_no升序排列会得到如下结果:
所以下列MySQL语句正确的是:
SELECT d.dept_no,d.dept_name,COUNT(salary) sum FROM departments d,dept_emp e,salaries s WHERE d.dept_no=e.dept_no AND e.emp_no=s.emp_no GROUP BY d.dept_no;
SELECT d.dept_no, d.dept_name, COUNT(s.salary) AS sum FROM salaries s JOIN dept_emp e ON s.emp_no = e.emp_no JOIN departments d ON d.dept_no = e.dept_no GROUP BY d.dept_no,d.dept_name ORDER BY d.dept_no;
SELECT d.dept_no, d.dept_name, SUM(s.salary) AS sum FROM salaries s JOIN dept_emp e ON s.emp_no = e.emp_no JOIN departments d ON d.dept_no = e.dept_no GROUP BY d.dept_no ORDER BY d.dept_no;
SELECT d.dept_no, d.dept_name, COUNT(s.salary) AS sum FROM salaries s JOIN dept_emp e ON s.emp_no = e.emp_no JOIN departments d ON d.dept_no = e.dept_no GROUP BY d.dept_no;
select a.dept_no dept_no,a.dept_name dept_name,count(*) sum from (select de.dept_no,de.dept_name,t.emp_no from departments de inner join (select s.emp_no, d.dept_no from dept_emp d inner join salaries s on d.emp_no = s.emp_no ) t on de.dept_no = t.dept_no) a group by a.dept_no