mysql将公司数据随机挂在部门身上

1.创建示例数据

CREATE TABLE department_table (
    company_code VARCHAR(10) COMMENT '公司编码',
    company_name VARCHAR(50) COMMENT '公司名称',
    department_code VARCHAR(10) COMMENT '部门编码',
    department_name VARCHAR(50) COMMENT '部门名称',
    sales DECIMAL(10, 2) COMMENT '销售额'
);


CREATE TABLE company_profit_table (
    company_code VARCHAR(10) COMMENT '公司编码',
    company_name VARCHAR(50) COMMENT '公司名称',
    profit DECIMAL(10, 2) COMMENT '公司利润'
);


INSERT INTO department_table (company_code, company_name, department_code, department_name, sales) VALUES
('C001', 'Company A', 'D001', 'Dept A1', 1000.00),
('C001', 'Company A', 'D002', 'Dept A2', 2000.00),
('C002', 'Company B', 'D003', 'Dept B1', 3000.00),
('C002', 'Company B', 'D004', 'Dept B2', 4000.00),
('C003', 'Company C', 'D005', 'Dept C1', 5000.00);

INSERT INTO company_profit_table (company_code, company_name, profit) VALUES
('C001', 'Company A', 10000.00),
('C002', 'Company B', 20000.00),
('C003', 'Company C', 30000.00);

2.示例sql

WITH aggregated_profit AS (
    SELECT company_code, company_name, profit
    FROM company_profit_table
),
department_with_profit AS (
    SELECT d.company_code, d.company_name, d.department_code, d.department_name, d.sales, a.profit
    FROM department_table d
    LEFT JOIN aggregated_profit a ON d.company_code = a.company_code
),
ranked_departments AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY company_code ORDER BY sales DESC) AS sales_rank
    FROM department_with_profit
)
SELECT company_code, company_name, department_code, department_name, sales,
       CASE 
           WHEN sales_rank = 1 THEN profit
           ELSE NULL
       END AS profit
FROM ranked_departments;

欢迎大佬指正，更优的方式。