table
1 num1
1 num2
1 num3
1 num4
2 num5
2 num6
2 num7
3 num8
3 num9
4 num10
类似这样一个表 我要得到这个结果:
1 4
2 3
3+4 3
请问要用一句SQL文得到这样的结果,该怎么写,请高手指点!
1 num1
1 num2
1 num3
1 num4
2 num5
2 num6
2 num7
3 num8
3 num9
4 num10
类似这样一个表 我要得到这个结果:
1 4
2 3
3+4 3
请问要用一句SQL文得到这样的结果,该怎么写,请高手指点!
+------+-------+
| id | name |
+------+-------+
| 1 | num1 |
| 1 | num2 |
| 1 | num3 |
| 1 | num4 |
| 2 | num5 |
| 2 | num6 |
| 2 | num7 |
| 3 | num8 |
| 3 | num9 |
| 4 | num10 |
+------+-------+
10 rows in set (0.00 sec)mysql>
mysql> select if(id=3 or id=4,'3+4',id) as id,
-> count(*)
-> from t_usnay
-> group by if(id=3 or id=4,'3+4',id);
+------+----------+
| id | count(*) |
+------+----------+
| 1 | 4 |
| 2 | 3 |
| 3+4 | 3 |
+------+----------+
3 rows in set (0.02 sec)mysql>
-> when 4 then '3+4'
-> else id
-> end as nid,
-> count(*)
-> from t_usnay
-> group by nid;
+------+----------+
| nid | count(*) |
+------+----------+
| 1 | 4 |
| 2 | 3 |
| 3+4 | 3 |
+------+----------+
3 rows in set (0.00 sec)mysql>