我有表t内容格式如下
select * from t;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 21 | 1 |
| 21 | 2 |
| 21 | 3 |
| 21 | 4 |
| 2 | 1 |
| 3 | 5 |
| 3 | 8 |
| 3 | 8 |
+----+---+
执行 select count(a), count_b from (select a, count(b) as count_b from t group
by a) as tt group by count_b;结果如下
+----------+---------+
| count(a) | count_b |
+----------+---------+
| 1 | 1 |
| 1 | 3 |
| 2 | 4 |
+----------+---------+
请问如何优化以上查询SQL语句?
select * from t;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 21 | 1 |
| 21 | 2 |
| 21 | 3 |
| 21 | 4 |
| 2 | 1 |
| 3 | 5 |
| 3 | 8 |
| 3 | 8 |
+----+---+
执行 select count(a), count_b from (select a, count(b) as count_b from t group
by a) as tt group by count_b;结果如下
+----------+---------+
| count(a) | count_b |
+----------+---------+
| 1 | 1 |
| 1 | 3 |
| 2 | 4 |
+----------+---------+
请问如何优化以上查询SQL语句?
+----------+---------+
| count(a) | count_b |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 2 | 4 |
+----------+---------+
就是这条SQL语句怎么优化?
表如下:
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| computer | int(10) unsigned | NO | | NULL | |
| ip | int(10) unsigned | NO | | NULL | |
统计产生1个IP的计算机有多少台……到产生n个IP的计算机有多少台
表如下:
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| computer | int(10) unsigned | NO | | NULL | |
| ip | int(10) unsigned | NO | | NULL | |
统计产生1个IP的计算机有多少台……到产生n个IP的计算机有多少台
group by computer
order by count(ip)
;不就可以满足你的需求了吗?把每个计算机分配的ip地址排序即可啊