我有一个mysql表的以下布局:
+------+-------+-----+------+
| user |subject|month|change|
+------+-------+-----+------+
|Donald| Math | Jan | 3.15 |
+------+-------+-----+------+
| Mike | Math | Jan | 2.15 |
+------+-------+-----+------+
|Regan | Math | Jan | 3.00 |
+------+-------+-----+------+
|Donald| Engl | Febr|-3.05 |
+------+-------+-----+------+
| Mike | Engl | Febr| 3.00 |
+------+-------+-----+------+
|Regan | Engl | Febr|-3.00 |
+------+-------+-----+------+
|Donald| Geog | Jan | 3.00 |
+------+-------+-----+------+
| Mike | Geog | Jan |-2.15 |
+------+-------+-----+------+
|Regan | Geog | Jan | 3.60 |
+------+-------+-----+------+
我需要按照科目统计正负变化的组,并获取该组的名称。如果我像这样查询mysql表:
COUNT (*) FROM $table WHERE change>0 GROUP BY subject
它只会统计每个科目列中正数变化的数量(>0)。无法获取负数变化(<0)和获取组名(Math、Eng和Geog)的数量。
我希望结果应该像这样:
===============
Group | >0| <0|
===============
Math | 3 | 0 |
---------------
Eng | 1 | 2 |
---------------
Geog | 2 | 1 |
---------------
有什么单一的mysql查询可以得到像上面那样的结果吗?