-- 建表
CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2)
);

INSERT INTO department VALUES (1, '技术部'), (2, '市场部'), (3, '财务部');
INSERT INTO employee VALUES 
(1, '张三', 1, 15000),
(2, '李四', 1, 13000),
(3, '王五', 2, 11000),
(4, '赵六', NULL, 9000);  -- 赵六没有部门

-- 内连接：只显示有部门的员工
SELECT e.name, e.salary, d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.id;
-- 结果：张三、李四、王五（赵六被过滤掉）

-- 查询所有员工及其部门信息（包括无部门的员工）
SELECT e.name, e.salary, d.dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.id;
-- 结果：赵六的 dept_name 为 NULL

-- 查找没有分配部门的员工
SELECT e.name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
WHERE d.id IS NULL;
-- 结果：赵六

SELECT e.name, d.dept_name
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.id;
-- 即使某个部门没有员工，也会显示该部门

-- 模拟全连接：所有员工和所有部门都显示
SELECT e.name, d.dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
UNION
SELECT e.name, d.dept_name
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.id;

-- 查询工资高于平均工资的员工
SELECT name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

-- 查询每个员工及其部门平均工资的差额
SELECT name, salary,
       salary - (SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id) AS diff
FROM employee e;

-- 查询与张三同部门且同薪水的员工
SELECT name, dept_id, salary
FROM employee
WHERE (dept_id, salary) = (
    SELECT dept_id, salary FROM employee WHERE name = '张三'
) AND name != '张三';

-- 查询每个部门工资最高的员工
SELECT d.dept_name, t.name, t.salary
FROM (
    SELECT dept_id, name, salary,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employee
) t
JOIN department d ON t.dept_id = d.id
WHERE t.rk = 1;

-- 查询工资高于所在部门平均工资的员工
SELECT e1.name, e1.salary, e1.dept_id
FROM employee e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employee e2
    WHERE e2.dept_id = e1.dept_id
);

-- 统计每个部门的员工数和平均工资
SELECT d.dept_name,
       COUNT(e.id) AS emp_count,
       ROUND(AVG(e.salary), 2) AS avg_salary,
       MAX(e.salary) AS max_salary,
       MIN(e.salary) AS min_salary
FROM department d
LEFT JOIN employee e ON d.id = e.dept_id
GROUP BY d.id, d.dept_name;

-- WHERE：分组前过滤掉工资低于10000的员工
-- HAVING：分组后过滤掉平均工资低于12000的部门
SELECT d.dept_name,
       COUNT(e.id) AS emp_count,
       ROUND(AVG(e.salary), 2) AS avg_salary
FROM department d
JOIN employee e ON d.id = e.dept_id
WHERE e.salary >= 10000          -- 分组前过滤
GROUP BY d.id, d.dept_name
HAVING AVG(e.salary) >= 12000    -- 分组后过滤
ORDER BY avg_salary DESC;

-- 建表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product VARCHAR(50),
    category VARCHAR(20),
    amount DECIMAL(12,2),
    sale_date DATE,
    region VARCHAR(20)
);

INSERT INTO sales VALUES
(1, '笔记本', '电子产品', 8000, '2024-01-15', '华东'),
(2, '手机', '电子产品', 5000, '2024-01-16', '华东'),
(3, '笔记本', '电子产品', 8000, '2024-02-10', '华南'),
(4, '键盘', '配件', 200, '2024-02-11', '华东'),
(5, '手机', '电子产品', 5500, '2024-03-05', '华北'),
(6, '笔记本', '电子产品', 8200, '2024-03-08', '华东'),
(7, '鼠标', '配件', 50, '2024-03-10', '华南');

-- 需求：查询每季度每个类别销售额 > 10000 的类别，且只统计华东地区的订单
SELECT 
    QUARTER(sale_date) AS quarter,
    category,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM sales
WHERE region = '华东'                     -- 先过滤地区
GROUP BY quarter, category
HAVING total_amount > 10000              -- 再过滤销售额
ORDER BY quarter, total_amount DESC;

-- UNION：去重合并（有排序开销）
-- UNION ALL：直接合并（性能更好）
SELECT name, '员工' AS type FROM employee
UNION ALL
SELECT dept_name, '部门' FROM department;

-- 实际场景：合并历史订单和当月订单
SELECT order_id, amount, '历史' AS source FROM orders_2023
UNION ALL
SELECT order_id, amount, '当月' FROM orders_2024;

-- 查询各部门工资排名前2的员工（跨部门统一排名）
SELECT name, dept_id, salary, '高薪' AS level
FROM employee
WHERE salary >= 15000
UNION ALL
SELECT name, dept_id, salary, '中等'
FROM employee
WHERE salary BETWEEN 10000 AND 14999
UNION ALL
SELECT name, dept_id, salary, '初级'
FROM employee
WHERE salary < 10000
ORDER BY salary DESC;

-- 建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    register_date DATE
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(12,2),
    order_date DATETIME,
    status VARCHAR(20)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT
);

-- 插入示例数据
INSERT INTO users VALUES
(1, '张三', '2024-01-01'),
(2, '李四', '2024-02-15'),
(3, '王五', '2024-03-20');

INSERT INTO orders VALUES
(1, 1, 5000, '2024-06-01 10:00:00', '已完成'),
(2, 1, 3000, '2024-06-15 14:00:00', '已完成'),
(3, 2, 8000, '2024-07-01 09:00:00', '已完成'),
(4, 3, 2000, '2024-07-10 16:00:00', '已取消');

INSERT INTO order_items VALUES
(1, 1, '手机', 5000, 1),
(2, 2, '键盘', 200, 2),
(3, 2, '鼠标', 50, 4),
(4, 3, '笔记本', 8000, 1),
(5, 4, 'U盘', 100, 20);

-- 综合查询1：用户消费排行（仅统计已完成订单）
SELECT 
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent,
    ROUND(AVG(o.amount), 2) AS avg_order_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = '已完成'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

-- 综合查询2：查询客单价高于平均的订单详情
SELECT o.id, u.name, o.amount, o.order_date
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > (
    SELECT AVG(amount) FROM orders WHERE status = '已完成'
)
AND o.status = '已完成'
ORDER BY o.amount DESC;

-- 综合查询3：查询每个用户最近的一笔订单
SELECT u.name, t.amount, t.order_date
FROM (
    SELECT user_id, amount, order_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
    FROM orders
    WHERE status = '已完成'
) t
JOIN users u ON t.user_id = u.id
WHERE t.rn = 1;

-- 综合查询4：查询有订单但从未购买过电子产品的用户
SELECT DISTINCT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id NOT IN (
    SELECT DISTINCT o2.user_id
    FROM orders o2
    JOIN order_items oi ON o2.id = oi.order_id
    WHERE oi.product_name IN ('手机', '笔记本', '平板')
);