$query = "SELECT DISTINCT MONTH(salary_date) as month FROM salary_records ORDER BY month ASC"含义
时间: 2024-05-30 17:10:26 浏览: 8
这段代码是一个 SQL 查询语句,用于从名为 "salary_records" 的表中获取所有不同的月份,并按升序排序。具体解释如下:
- SELECT DISTINCT:选择唯一的结果,去除重复的月份。
- MONTH(salary_date) as month:从 "salary_date" 字段中提取月份,并将其重命名为 "month"。
- FROM salary_records:指定查询的表为 "salary_records"。
- ORDER BY month ASC:按照 "month" 字段的升序排序。
相关问题
3. 在MySQL样本数据库上完成下列查询: (1) 查询每个部门(departments)的编号(dept_no),名称(dept_name),在该部门工作过的雇员(employees)人数,最低工资(salary),平均工资,最高工资及工资总额; (2) 查询每个部门(departments)的编号(dept_no),名称(dept_name),及各个时间段(from_date,to_date)担任该部门经理(dept_manager)的雇员的编号(emp_no)和姓名(first_name+last_name),并按时间段先后显式; (3) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工资额(salary),并按时间段先后显式; (4) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工作部门名称(dept_name),并按时间段先后显式; (5) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及任职过的部门数; (6) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)担任的职务(title),并按时间段先后显式;
(1) 查询每个部门(departments)的编号(dept_no),名称(dept_name),在该部门工作过的雇员(employees)人数,最低工资(salary),平均工资,最高工资及工资总额;
```
SELECT d.dept_no, d.dept_name, COUNT(e.emp_no) AS num_of_employees, MIN(s.salary) AS min_salary, AVG(s.salary) AS avg_salary, MAX(s.salary) AS max_salary, SUM(s.salary) AS total_salary
FROM departments d
LEFT JOIN dept_emp de ON d.dept_no = de.dept_no
LEFT JOIN employees e ON de.emp_no = e.emp_no
LEFT JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY d.dept_no, d.dept_name;
```
(2) 查询每个部门(departments)的编号(dept_no),名称(dept_name),及各个时间段(from_date,to_date)担任该部门经理(dept_manager)的雇员的编号(emp_no)和姓名(first_name+last_name),并按时间段先后显式;
```
SELECT d.dept_no, d.dept_name, dm.from_date, dm.to_date, e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name
FROM departments d
LEFT JOIN dept_manager dm ON d.dept_no = dm.dept_no
LEFT JOIN employees e ON dm.emp_no = e.emp_no
ORDER BY dm.from_date ASC;
```
(3) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工资额(salary),并按时间段先后显式;
```
SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, s.from_date, s.to_date, s.salary
FROM employees e
LEFT JOIN salaries s ON e.emp_no = s.emp_no
ORDER BY s.from_date ASC;
```
(4) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工作部门名称(dept_name),并按时间段先后显式;
```
SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, dp.from_date, dp.to_date, d.dept_name
FROM employees e
LEFT JOIN dept_emp dp ON e.emp_no = dp.emp_no
LEFT JOIN departments d ON dp.dept_no = d.dept_no
ORDER BY dp.from_date ASC;
```
(5) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及任职过的部门数;
```
SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, COUNT(DISTINCT dp.dept_no) AS num_of_departments
FROM employees e
LEFT JOIN dept_emp dp ON e.emp_no = dp.emp_no
GROUP BY e.emp_no, emp_name;
```
(6) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)担任的职务(title),并按时间段先后显式;
```
SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, tt.from_date, tt.to_date, t.title
FROM employees e
LEFT JOIN titles t ON e.emp_no = t.emp_no
LEFT JOIN titles tt ON t.emp_no = tt.emp_no AND tt.from_date < t.from_date
GROUP BY e.emp_no, tt.from_date, tt.to_date, t.title
ORDER BY tt.from_date ASC;
```
有一个薪水表salaries简况如下: emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 43311 2001-12-01 9999-01-01 10004 72527 2001-12-01 9999-01-01 对所有员工的薪水按照salary降序先进行1-N的排名,如果salary相同,再按照emp_no升序排列,但二者t_rank要相同: emp_no salary t_rank 10001 88958 1 10002 72527 2 10004 72527 2 10003 43311 3
可以使用MySQL语句来实现这个功能:
```
SELECT emp_no, salary,
(SELECT COUNT(DISTINCT salary) FROM salaries s2 WHERE s2.salary >= s1.salary) AS t_rank
FROM salaries s1
ORDER BY salary DESC, emp_no ASC;
```
这个语句首先按照salary降序排序,然后使用子查询来计算当前salary的t_rank,也就是比当前salary大或等于的唯一salary的数量。最后按照salary和emp_no排序即可。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![ppt](https://img-home.csdnimg.cn/images/20210720083527.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)