数据:
---------
id hour
1 8
2 7
2 8
3 9
3 9
3 10
4 6
4 13
5 8
5 9
6 6
6 23
-------
想写2个查询,
第一个查询,最终希望获得结果
-----
id hour
1 8
2 7
3 9
4 6
5 8
6 6
-----
就是说,distinct(id)后,取该id中hour最小的一个,其它都不要第二个查询,最终希望获得结果
-----
hour count(id)
6 2
7 1
8 2
9 1--------
统计第一次查询结果中,hour为6出现的id次数。基于原始数据,该怎么写?
---------
id hour
1 8
2 7
2 8
3 9
3 9
3 10
4 6
4 13
5 8
5 9
6 6
6 23
-------
想写2个查询,
第一个查询,最终希望获得结果
-----
id hour
1 8
2 7
3 9
4 6
5 8
6 6
-----
就是说,distinct(id)后,取该id中hour最小的一个,其它都不要第二个查询,最终希望获得结果
-----
hour count(id)
6 2
7 1
8 2
9 1--------
统计第一次查询结果中,hour为6出现的id次数。基于原始数据,该怎么写?
+------+------+
| id | hour |
+------+------+
| 1 | 8 |
| 2 | 7 |
| 2 | 8 |
| 3 | 9 |
| 3 | 9 |
| 3 | 10 |
| 4 | 6 |
| 4 | 13 |
| 5 | 8 |
| 5 | 9 |
| 6 | 6 |
| 6 | 23 |
+------+------+
12 rows in set (0.00 sec)mysql> select id,min(`hour`) as min_hour from t_daguaio_O group by id;
+------+----------+
| id | min_hour |
+------+----------+
| 1 | 8 |
| 2 | 7 |
| 3 | 9 |
| 4 | 6 |
| 5 | 8 |
| 6 | 6 |
+------+----------+
6 rows in set (0.05 sec)mysql> select min_hour,count(*)
-> from (
-> select id,min(`hour`) as min_hour from t_daguaio_O group by id) t
-> group by min_hour;
+----------+----------+
| min_hour | count(*) |
+----------+----------+
| 6 | 2 |
| 7 | 1 |
| 8 | 2 |
| 9 | 1 |
+----------+----------+
4 rows in set (0.08 sec)mysql>
select
*
from
tb t
where
not exists(select 1 from tb where id=t.id and `hour`<t.`hour`)--2.
select
`hour`,
count(1) as `count(id)`
from
tb t
where
not exists(select 1 from tb where id=t.id and `hour`<t.`hour`)
group by
`hour`
order by
`hour`
select id,min(`hour`) as `field_hour` from 表 group by id;写法b:
select id,`hour` from 表 a where not exists (select 1 from 表 b where b.id=a.id and b.`hour`<a.`hour`);-- ---------------------------------------------
查询2:写法a:
select tt.field_hour,count(*) from
(
select id,min(`hour`) as `field_hour` from 表 group by id;
) tt
where tt.field_hour=6;写法b:
select `hour`,count(*) from 表 a where not exists (select 1 from 表 b where b.id=a.id and b.`hour`<a.`hour`) and a.`hour`=6
(select testx.id,min(hour) m from testx group by testx.id ) a
join
(select testx.id,count(id) c from testx group by testx.id ) b
on a.id=b.id and a.m=6
from 表
group by id2、select hour ,count(id)
from 表
where hour =6