mysql数据库两张表分组比较

-- 创建示例表A
CREATE TABLE tableA (
    id INT,
    name VARCHAR(50),
    category VARCHAR(50),
    value INT
);

-- 创建示例表B
CREATE TABLE tableB (
    id INT,
    name VARCHAR(50),
    category VARCHAR(50),
    value INT
);

-- 向表A插入示例数据
INSERT INTO tableA (id, name, category, value)
VALUES (1, 'A', 'Category1', 100),
       (2, 'B', 'Category2', 200),
       (3, 'C', 'Category1', 300);

-- 向表B插入示例数据
INSERT INTO tableB (id, name, category, value)
VALUES (1, 'A', 'Category1', 100),
       (2, 'B', 'Category2', 250),
       (3, 'C', 'Category1', 300);

-- 对表A和表B进行分组比较
SELECT tableA.category,
       SUM(tableA.value) AS total_value_A,
       SUM(tableB.value) AS total_value_B
FROM tableA
JOIN tableB ON tableA.category = tableB.category
GROUP BY tableA.category;