- 论坛徽章:
- 0
|
mysql> select * from t1;
+------+------+------+
| id | a | b |
+------+------+------+
| 1 | 1 | 3 |
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 4 |
| 2 | 1 | 1 |
| 2 | 1 | 3 |
| 2 | 2 | 2 |
| 2 | 2 | 4 |
+------+------+------+
8 rows in set (0.01 sec)
mysql> select t1.id,t1.a,min(t1.b),t2.num from t1 left join (select id,a,count(b) num from t1 group by id,a)t2 on t1.id=t2.id group by t1.id,t1.a;
+------+------+-----------+------+
| id | a | min(t1.b) | num |
+------+------+-----------+------+
| 1 | 1 | 1 | 2 |
| 1 | 2 | 2 | 2 |
| 2 | 1 | 1 | 2 |
| 2 | 2 | 2 | 2 |
+------+------+-----------+------+
4 rows in set (0.02 sec)
mysql> |
|