mysql - 使用 group by 进行复杂的 MySQL 查询

Question

假设我有两张 table

Table1:
product_id, design1, design2
1              A        C
2              B        A

Table2:
product_id, value
1             10
2             10

现在我想总结所有产品特定设计的所有值(value)。

SELECT designA, SUM(value) FROM ( 
 SELECT b.design1 AS designA, SUM(value) AS value FROM table2 AS a LEFT JOIN table1 AS b ON a.product_id = b.product_id GROUP BY b.design1) AS T GROUP BY designA

It gives me this:
designA SUM(value)
  A           10
  B           10

现在的问题是，如果用户在table1中指定了design2，那么design1的值将自动添加到design2中。如果 design2 不存在 design1 列，那么它将是结果的新行:

指定的结果是这样的:

designA SUM(value)
  A            20
  B            10
  C            10

Answer 1

select y.designA, sum(value) from 

(select  a.design1 as designA, value from
Table1 as a
inner join Table2 as b
on
a.product_id = b.product_id

union all

select a.design2 as designA, value from
Table1 as a
inner join Table2 as b
on
a.product_id = b.product_id) as y 
group by y.designA

似乎适用于您的测试数据，没有在其他配置上尝试过，但如果您了解它在做什么，您应该能够调整它。