SQL语言查询使用心得 -2

已于 2024-11-04 21:31:49 修改
阅读量232 收藏 3
点赞数 7
文章标签： sql 数据库 java
于 2024-11-04 21:30:37 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/xc_pt/article/details/143494563
版权

使用SQL的各种特性来处理复杂的查询需求

所用到的表

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    age INT,
    salary DECIMAL(10,2),
    department_id INT,
    hire_date DATE
);

CREATE TABLE language_skills (
    employee_id INT,
    language VARCHAR(50),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TABLE roles (
    role_id INT PRIMARY KEY,
    role_name VARCHAR(50)
);

CREATE TABLE employee_roles (
    employee_id INT,
    role_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

CREATE TABLE project_assignments (
    employee_id INT,
    project_name VARCHAR(100),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TABLE sales (
    employee_id INT,
    sale_date DATE,
    sale_amount DECIMAL(10,2),
    product_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TABLE complaints (
    complaint_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TABLE performance_ratings (
    employee_id INT,
    rating VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_manager_id INT,
    completion_date DATE,
    customer_satisfaction DECIMAL(3,2),
    FOREIGN KEY (project_manager_id) REFERENCES employees(employee_id)
);

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
);

CREATE TABLE job_applications (
    candidate_id INT,
    job_id INT
);

CREATE TABLE room_bookings (
    booking_id INT PRIMARY KEY,
    room_id INT,
    start_time DATETIME,
    end_time DATETIME
);

CREATE TABLE employee_shifts (
    employee_id INT,
    shift_date DATE,
    start_time TIME,
    end_time TIME,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TABLE attendance (
    employee_id INT,
    attendance_date DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

1.查找同时满足多个条件的记录

这类查询通常涉及多个条件的组合，可以使用 AND, OR, IN, EXISTS 等操作符。

示例1：查找同时会说英语和法语的员工

SELECT employee_name
FROM employees
WHERE employee_id IN (SELECT employee_id FROM language_skills WHERE language = 'English')
  AND employee_id IN (SELECT employee_id FROM language_skills WHERE language = 'French');

示例2：查找既是管理者又是技术专家的员工

SELECT e.employee_name
FROM employees e
JOIN employee_roles er ON e.employee_id = er.employee_id
JOIN roles r ON er.role_id = r.role_id
WHERE r.role_name IN ('Manager', 'Technical Expert')
GROUP BY e.employee_id
HAVING COUNT(DISTINCT r.role_id) = 2;

2.查找满足某个条件但不满足另一个条件的记录

这类查询通常使用 NOT IN, NOT EXISTS, 或者左连接加 IS NULL 来实现。

示例1：查找参加了项目A但没有参加项目B的员工

SELECT employee_name
FROM employees
WHERE employee_id IN (SELECT employee_id FROM project_assignments WHERE project_name = 'A')
  AND employee_id NOT IN (SELECT employee_id FROM project_assignments WHERE project_name = 'B');

示例2：查找有销售记录但没有收到投诉的员工

SELECT e.employee_name
FROM employees e
JOIN sales s ON e.employee_id = s.employee_id
LEFT JOIN complaints c ON e.employee_id = c.employee_id
WHERE c.complaint_id IS NULL;

3.查找满足多个复合条件的记录

这类查询通常涉及多个表的连接和复杂的条件组合。

示例1：查找年龄超过30岁，工资高于部门平均水平，且绩效评级为"优秀"的员工

SELECT e.employee_name
FROM employees e
JOIN performance_ratings pr ON e.employee_id = pr.employee_id
WHERE e.age > 30
  AND pr.rating = 'Excellent'
  AND e.salary > (
      SELECT AVG(salary)
      FROM employees
      WHERE department_id = e.department_id
  );

示例2：查找在过去一年内完成了至少3个项目，且客户满意度平均分大于4的项目经理

SELECT e.employee_name
FROM employees e
JOIN projects p ON e.employee_id = p.project_manager_id
WHERE p.completion_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY e.employee_id
HAVING COUNT(DISTINCT p.project_id) >= 3 AND AVG(p.customer_satisfaction) > 4;

4.查找重复记录

这类查询通常使用 GROUP BY 和 HAVING 子句来识别重复项。

示例1：查找重复的订单

SELECT order_id, customer_id, order_date, COUNT(*) as order_count
FROM orders
GROUP BY order_id, customer_id, order_date
HAVING COUNT(*) > 1;

示例2：查找多次申请同一职位的候选人

SELECT candidate_id, job_id, COUNT(*) as application_count
FROM job_applications
GROUP BY candidate_id, job_id
HAVING COUNT(*) > 1;

5.查找最新/最旧的记录

这类查询通常涉及子查询或窗口函数。

示例1：查找每个部门最近入职的员工

SELECT e.*
FROM employees e
INNER JOIN (
    SELECT department_id, MAX(hire_date) as latest_hire_date
    FROM employees
    GROUP BY department_id
) latest ON e.department_id = latest.department_id
        AND e.hire_date = latest.latest_hire_date;

示例2：使用窗口函数查找每个客户最近的订单

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
) ranked
WHERE rn = 1;

6.累积求和或移动平均

这类查询通常使用窗口函数或自连接。

示例1：计算员工的累积工资总和

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) as cumulative_salary
FROM employees;

示例2：计算产品的3个月移动平均销售额

SELECT 
    product_id,
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3_months
FROM sales;

7.查找区间重叠

这类查询通常涉及日期或数值范围的比较。

示例1：查找时间重叠的会议室预订

SELECT a.booking_id as booking1, b.booking_id as booking2, a.room_id
FROM room_bookings a
JOIN room_bookings b ON a.room_id = b.room_id
    AND a.booking_id < b.booking_id
    AND a.start_time < b.end_time
    AND b.start_time < a.end_time;

示例2：查找工作时间有重叠的员工

SELECT e1.employee_id as emp1, e2.employee_id as emp2
FROM employee_shifts e1
JOIN employee_shifts e2 ON e1.shift_date = e2.shift_date
    AND e1.employee_id < e2.employee_id
    AND e1.start_time < e2.end_time
    AND e2.start_time < e1.end_time;

8.查找连续的记录

这类查询通常涉及自连接或窗口函数。

示例1：查找连续三天都有销售的产品

SELECT DISTINCT a.product_id
FROM sales a
JOIN sales b ON a.product_id = b.product_id AND DATEDIFF(b.sale_date, a.sale_date) = 1
JOIN sales c ON a.product_id = c.product_id AND DATEDIFF(c.sale_date, a.sale_date) = 2;

示例2：使用窗口函数查找连续出勤的员工

WITH attendance_gaps AS (
    SELECT 
        employee_id,
        attendance_date,
        DATEDIFF(attendance_date, 
                 LAG(attendance_date) OVER (PARTITION BY employee_id ORDER BY attendance_date)
        ) as days_gap
    FROM attendance
)
SELECT DISTINCT employee_id
FROM attendance_gaps
WHERE days_gap = 1
GROUP BY employee_id
HAVING COUNT(*) >= 2;

最后祝各位程序员,代码无bug,事业向上!!!

SWlker
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值