php - MySQL SUM 同一行中的多列

Question

+---------------+---------+-----------------+---------+ 
| product_count | size1   | sproduct_count2 | size2
+---------------+---------+-----------------+---------+ 
|            13 | 2x4     |               5 |     2x6 
|            14 | 2x6     |               2 |     4x8 
|            15 | 2x8     |               3 |     2x8 
|            16 | 4x4     |               2 |     4x4 
|            17 | 4x8     |              15 |     4x8
+---------------+---------+-----------------+---------+

如何获得总计数以便返回结果如下所示:

product_count | size
           13 | 2x4
           19 | 2x6
           18 | 2x8
           18 | 4x4
           34 | 4x8

我试过:

SELECT SUM(product_count+product_count2) AS product_count, size1 FROM UNITS GROUP BY size1

它对某些行有效，对其他行无效。 CONCAT 是我需要使用的东西吗？

Answer 1

您需要对 2 列的并集进行聚合:

select sum(t.product_count) product_count, t.size from (
  select product_count, size1 size from units
  union all
  select sproduct_count2, size2 from units
) t
group by t.size

参见 demo .
结果:

| product_count | size |
| ------------- | ---- |
| 13            | 2x4  |
| 19            | 2x6  |
| 18            | 2x8  |
| 18            | 4x4  |
| 34            | 4x8  |