我有以下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查询才能获得上面的结果?
下面的查询将按记录分组subject
.MySQL支持布尔运算,因为a.change > 0
返回1表示true,0表示false表示.
SELECT a.Subject, SUM(a.change > 0) `> 0`, SUM(a.change < 0) `< 0` FROM tableName a GROUP BY a.Subject
SQLFiddle演示
请注意,它不包括主题change = 0
.