SQL 常用操作怎么写？COMPANY 表案例（子查询 / GROUP BY / 开窗函数）

CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, -- 员工ID（主键，非空） NAME TEXT NOT NULL, -- 员工姓名（非空） AGE INT NOT NULL, -- 员工年龄（非空） ADDRESS CHAR(50), -- 员工地址 SALARY REAL -- 员工薪资 ); 

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ), (2, 'Allen', 25, 'Texas', 15000.00 ), (3, 'Teddy', 23, 'Norway', 20000.00 ), (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ), (5, 'David', 27, 'Texas', 85000.00 ), (6, 'Kim', 22, 'South-Hall', 45000.00 ), (7, 'James', 24, 'Houston', 10000.00 ), (8, 'Paul', 24, 'Houston', 20000.00 ), (9, 'James', 44, 'Norway', 5000.00 ), (10, 'James', 45, 'Texas', 5000.00 ); 

CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); 

INSERT INTO COMPANY1 VALUES (8, 'Paul', 24, 'Houston', 20000.00 ), (9, 'James', 44, 'Norway', 5000.00 ), (10, 'James', 45, 'Texas', 5000.00 ); 

CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, -- 部门关联ID DEPT CHAR(50) NOT NULL, -- 部门名称 EMP_ID INT NOT NULL -- 关联员工ID（对应COMPANY的ID） ); 

INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (1,'IT Billing',1 ), (2,'Engineering',2 ), (3,'Finance',7 ); 

select * from COMPANY; 

select * from company order by name desc, salary asc; 

select age, avg(salary) from company group by age; 

select name from company group by name having count(*)>1; -- 筛选分组后数量>1的姓名（即重名） 

select distinct name from company; 

with recursive t(n) as( -- recursive用于递归查询 values(0) -- 初始值 union all select salary from company where salary <20000 -- 递归获取符合条件的薪资 ) select sum(n) from t; -- 计算总和 

with moved_rows as ( delete from company where salary >= 30000 RETURNING * -- 返回删除的行 ) insert into company1 (select * from moved_rows); -- 插入删除的行到COMPANY1 

-- 1. 全局窗口（无分区，所有数据为一个窗口） sum(salary) OVER () -- 所有员工薪资总和 sum(salary) OVER (ORDER BY salary) -- 按薪资升序，累积求和 -- 2. 分区窗口（按部门分区，每个部门为一个窗口） avg(salary) OVER (PARTITION BY depname) -- 按部门求平均薪资 rank() OVER (PARTITION BY depname ORDER BY salary DESC) -- 按部门内薪资降序排名 -- 3. 窗口别名（重复窗口逻辑时简化代码） SELECT sum(salary) OVER w, -- 复用窗口w avg(salary) OVER w FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); -- 定义窗口w