如何通过WORKDEPT字段关联DEPT表，实现按薪资排序员工姓名与部门名称？

No problem at all—you’re on the right track with using the WORKDEPT field to link the two tables. Here’s a straightforward way to pull the data you need and sort it by salary:

Basic INNER JOIN Query #

Assuming your employee table is named EMPLOYEE (with columns like EMPNAME for the employee’s name, WORKDEPT for their department code, and SALARY for their pay), and your department table is DEPT (with DEPTNO matching WORKDEPT and DEPTNAME for the department’s full name), this query will do the trick:

SELECT 
  e.EMPNAME AS EmployeeName,
  d.DEPTNAME AS DepartmentName,
  e.SALARY
FROM EMPLOYEE e
INNER JOIN DEPT d 
  ON e.WORKDEPT = d.DEPTNO
ORDER BY e.SALARY DESC; -- Use ASC instead of DESC for lowest to highest salary

Breakdown of the Query #

• SELECT: We’re grabbing the employee name, department name, and salary (including aliases like EmployeeName makes the output easier to read).
• INNER JOIN: This ensures we only get employees who have a valid department entry in the DEPT table. If you need to include employees who don’t have a linked department (e.g., NULL in WORKDEPT), swap INNER JOIN with LEFT JOIN instead.
• ON: This is where we link the two tables using the matching department code (WORKDEPT from employee table = DEPTNO from department table).
• ORDER BY: Sorts the final results by salary. Using DESC gives you highest salary first; change to ASC for lowest to highest.

Quick Notes #

• Make sure the WORKDEPT and DEPTNO columns are the same data type (e.g., both VARCHAR or INTEGER) to avoid join errors.
• If your table/column names are different (e.g., EMP_NAME instead of EMPNAME), just adjust those to match your actual schema.

内容的提问来源于stack exchange，提问作者False King