MySQL 按一列分组，但选择所有其他数据并将其求和

Question

我有一个表，如下所示:

studentid | studentname | fee | latfeecharges | dues
1         | Alex        | 2000 | 200           | 500
1         | Alex        | 2000 | 200           | 500
1         | Alex        | 2000 | 200           | 500

我想要以下结果:

Alex

2700
2700
2700

因此，在这种情况下，fee、latefeecharges 和 dues 相加，我分别获取它们，但只想获取名称时间。我该怎么做？

现在我通过使用这个查询得到这个结果

$query = "SELECT * FROM table  ORDER BY studentid ";

alex2700alex2700alex2700

Answer 1

假设您的表中有一个月或以某种方式导出它，您可以使用条件聚合如此给出

MariaDB [sandbox]> select * from t;
+-----------+-------------+------+----------------+------+------+
| studentid | studentname | fee  | latefeecharges | dues | MTH  |
+-----------+-------------+------+----------------+------+------+
|         1 | Alex        | 2000 |            200 |  500 |    1 |
|         1 | Alex        | 2000 |            200 |  500 |    2 |
|         1 | Alex        | 2000 |            200 |  500 |    3 |
+-----------+-------------+------+----------------+------+------+
3 rows in set (0.00 sec)

MariaDB [sandbox]> select studentname,
    -> sum(case when mth = 1 then fee+latefeecharges+dues else 0 end) month1,
    -> sum(case when mth = 2 then fee+latefeecharges+dues else 0 end) month2,
    -> sum(case when mth = 3 then fee+latefeecharges+dues else 0 end) month3
    -> from t
    -> group by studentname;
+-------------+--------+--------+--------+
| studentname | month1 | month2 | month3 |
+-------------+--------+--------+--------+
| Alex        |   2700 |   2700 |   2700 |
+-------------+--------+--------+--------+
1 row in set (0.00 sec)