mysql - 连接表使值加倍

Question

我有这张表:

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY,
value1 INT NOT NULL,
value2 INT NOT NULL
);

CREATE TABLE table2 (
    id INT NOT NULL PRIMARY KEY,
    table1_id INT NOT NULL,
    valuex INT NOT NULL
);

INSERT INTO table1 (id, value1, value2)
VALUES
    (1, 10, 15),
    (2, 5 ,  3);

INSERT INTO table2 (id, table1_id, valuex)
VALUES
    (1, 1, 15),
    (2, 1, 25),
    (3, 2, 14),
    (4, 2, 10);

这样:

SELECT COUNT(`table1`.`id`) AS `orders`, 
   SUM(`value1`) as `sum_value1`, SUM(`value2`) as `sum_value2`, 
   SUM(`valuex`) as `sum_valuex` 
FROM `table1` 
     INNER JOIN `table2` 
         ON `table1`.`id` = `table2`.`table1_id`

我得到输出:

+----------------------------------------------+
+ orders | sum_value1 | sum_value2 |sum_valuex +
+----------------------------------------------+
+    4   |     30     |     36     |    64     +
+----------------------------------------------+

但是我在table1中只有两个订单。我知道由于连接而导致重复，但是如何通过添加 sum_valuex 来解决这个问题？

我想要的结果是:

+----------------------------------------------+
+ orders | sum_value1 | sum_value2 |sum_valuex +
+----------------------------------------------+
+    2   |     15     |     18     |    64     +
+----------------------------------------------+

编辑:我无法在选择中使用选择

Answer 1

这就是连接的工作原理。如果您不希望行在聚合之前相乘，请在执行连接之前进行聚合。

SELECT t2.orders, t1.value1, t1.value2, t2.sum_valuex
FROM `table1` INNER JOIN
     (SELECT table1_id, SUM(valuex) as sum_valuex, COUNT(*) as orders
      FROM table2
      GROUP BY table1_id
     ) t2
     ON t1.id = t2.table1_id