mysql update指定分类 取最新的3条数据 更新

Question

表table1，字段 id，city_id，yz
内容
id city_id yz
1 10 0
2 20 0
3 30 0
4 10 0
5 40 0
6 20 0
7 10 0
8 40 0
9 30 0
10 20 0
11 10 0
12 20 0
13 30 0
14 40 0
15 40 0
16 20 0
17 10 0
18 40 0
19 30 0
20 10 0
现在想把id最后数起，最后city_id 相同的 3个 把yz更新为1

就是下面结果
id city_id yz
20 10 1

17 10 1
11 10 1
16 20 1
10 20 1
12 20 1
19 30 1
13 30 1
9 30 1
18 40 1
15 40 1
14 40 1
1 10 0
2 20 0
3 30 0
4 10 0
6 20 0
5 40 0
7 10 0

8 40 0

Answer 1

update table1 set yz=1 where city_id in(
select city_id from(
select count(*) count,city_id from table1 group by city_id
)

)

分析，查询出了每一种city_id的数量,
select count(*) count,city_id from table1 group by city_id

查询出数量等于3的city_id
select city_id from(
select count(*) count,city_id from table1 group by city_id
) s where s.count=3

对这个数量等于3的yz进行修改

update table1 set yz=1 where city_id in(
select city_id from(
select count(*) count,city_id from table1 group by city_id
)

)
这就完成了