医院网站 整站源码,西安装修行业网站建设,重庆智能网站建设多少钱,电子商务网站建设与维护项目五文章目录 WITH 语句和临时表员工部门案例#xff08;单个 WITH 查询#xff09;建表#xff1a;写入数据#xff1a;使用WITH查询 扩展案例#xff1a;员工、部门、薪水和级别#xff08;多个 WITH 查询#xff09;建表和插入数据#xff1a;使用多个WITH查询 结语 WIT… 文章目录 WITH 语句和临时表员工部门案例单个 WITH 查询建表写入数据使用WITH查询 扩展案例员工、部门、薪水和级别多个 WITH 查询建表和插入数据使用多个WITH查询 结语 WITH 语句和临时表
在 SQL 中WITH 语句用于创建临时结果集该结果集在整个 SQL 查询过程中都可使用。这种结构被称为公用表表达式Common Table Expression简称 CTE特别适用于处理复杂的查询。通过 CTE可以将复杂查询拆分为更小、更易管理的部分。
员工部门案例单个 WITH 查询
考虑这样一个场景我们有两个表——员工表 (employees) 和部门表 (departments)。员工表记录了员工的 ID、姓名及其所属的部门 ID而部门表则包含部门的 ID 和名称。
建表
CREATE TABLE test.departments (department_id INT PRIMARY KEY,department_name VARCHAR(50)
);CREATE TABLE test.employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(50),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
写入数据
-- 向部门表中添加数据
INSERT INTO test.departments (department_id, department_name) VALUES
(1, IT),
(2, HR),
(3, Finance);-- 向员工表中添加数据
INSERT INTO test.employees (employee_id, employee_name, department_id) VALUES
(101, Alice, 1),
(102, Bob, 1),
(103, Charlie, 2),
(104, David, 3);
使用WITH查询
现在假设我们想要一个报告列出每个部门及其员工数。我们可以使用WITH语句来帮助我们组织查询 WITH DepartmentSummary AS (SELECT department_id, COUNT(*) AS employee_countFROM test.employeesGROUP BY department_id
)
SELECT d.department_name, ds.employee_count
FROM DepartmentSummary ds
JOIN tets.departments d ON ds.department_id d.department_id;
;
扩展案例员工、部门、薪水和级别多个 WITH 查询
员工表 (employees): 包含员工ID、姓名、部门ID。部门表 (departments): 包含部门ID和部门名。薪水表 (salaries): 包含员工ID和薪水。级别表 (levels): 包含员工ID和级别例如经理、高级员工等
建表和插入数据
CREATE TABLE test.salaries (employee_id INT,salary INT,FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);CREATE TABLE test.levels (employee_id INT,level VARCHAR(50),FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);-- 向薪水表和级别表中添加数据
INSERT INTO test.salaries (employee_id, salary) VALUES
(101, 70000),
(102, 80000),
(103, 50000),
(104, 60000);INSERT INTO test.levels (employee_id, level) VALUES
(101, Senior),
(102, Junior),
(103, Senior),
(104, Manager);使用多个WITH查询
现在我们要生成一个报告显示每个部门的平均薪水和每个级别的员工数量。我们将使用两个临时表来实现这一点
WITH DepartmentSalary AS (SELECT d.department_id, AVG(s.salary) AS average_salaryFROM test.employees eJOIN test.salaries s ON e.employee_id s.employee_idJOIN test.departments d ON e.department_id d.department_idGROUP BY d.department_id
),
LevelCount AS (SELECT l.level, COUNT(*) AS countFROM test.employees eJOIN test.levels l ON e.employee_id l.employee_idGROUP BY l.level
)
SELECT d.department_name, ds.average_salary, lc.level, lc.count
FROM DepartmentSalary ds
JOIN test.departments d ON ds.department_id d.department_id
CROSS JOIN LevelCount lc;
在这个查询中
DepartmentSalary 临时表计算每个部门的平均薪水。LevelCount 临时表计算每个级别的员工数。最后的查询将这两个临时表与部门表进行连接并使用CROSS JOIN来展示所有可能的部门和级别组合以及对应的平均薪水和员工数。
结语
在总结以上内容时我们可以看到 WITH 语句在 SQL 中的强大作用特别是在处理复杂的数据查询和报告生成时。通过使用公用表表达式 (CTE)我们不仅可以提高查询的可读性和维护性还可以有效地组织和简化复杂的查询逻辑。无论是在处理基础的员工部门数据还是在进行更复杂的多表查询如员工、部门、薪水和级别的案例中WITH 语句都展现了其灵活性和效能。正如这些示例所展示的掌握这些高级 SQL 技巧将大大增强数据处理和分析的能力对任何数据库专业人士或数据分析师都是宝贵的资产。
