表a数据如下
id code pname
1 HY001 b1
2 HY001 b2
3 HY002 c1
4 HY003 a1
4 HY003 a2
4 HY003 a3
5 HY004 f1
5 HY004 f2得到结果如下:
id code pname num
1 HY001 b1 1
2 HY001 b2 2
3 HY002 c1 1
4 HY003 a1 1
4 HY003 a2 2
4 HY003 a3 3
5 HY004 f1 1
5 HY004 f2 2要得到如上结果 请大家帮帮忙啊?
id code pname
1 HY001 b1
2 HY001 b2
3 HY002 c1
4 HY003 a1
4 HY003 a2
4 HY003 a3
5 HY004 f1
5 HY004 f2得到结果如下:
id code pname num
1 HY001 b1 1
2 HY001 b2 2
3 HY002 c1 1
4 HY003 a1 1
4 HY003 a2 2
4 HY003 a3 3
5 HY004 f1 1
5 HY004 f2 2要得到如上结果 请大家帮帮忙啊?
4 HY003 a2 2
4 HY003 a3 3按什么排的序?
+------+-------+-------+
| id | code | pname |
+------+-------+-------+
| 1 | HY001 | b1 |
| 2 | HY001 | b2 |
| 3 | HY002 | c1 |
| 4 | HY003 | a1 |
| 4 | HY003 | a2 |
| 4 | HY003 | a3 |
| 5 | HY004 | f1 |
| 5 | HY004 | f2 |
+------+-------+-------+
8 rows in set (0.00 sec)mysql>
mysql> select id , code , pname,
-> (select count(*) from a where code=t.code and pname<=t.pname) as num
-> from a t;
+------+-------+-------+------+
| id | code | pname | num |
+------+-------+-------+------+
| 1 | HY001 | b1 | 1 |
| 2 | HY001 | b2 | 2 |
| 3 | HY002 | c1 | 1 |
| 4 | HY003 | a1 | 1 |
| 4 | HY003 | a2 | 2 |
| 4 | HY003 | a3 | 3 |
| 5 | HY004 | f1 | 1 |
| 5 | HY004 | f2 | 2 |
+------+-------+-------+------+
8 rows in set (0.00 sec)mysql>