表a
d sname(名字) smoney(业绩) sprovince(地区)
1 zhangsan 2098 A
2 lisi 3000 B
3 wangwu 6789 C
4 liumazi 4587 C
5 dongjiu 3298 B
6 shiga 4567 A显示出 业绩 大于同一地区平均值的 合同id、姓名、业绩和地区表b
id province city Area population
1 Guangdong Yangjiang 4000 4000000
2 Guangdong Guangzhou 6000 12000000
3 Guangdong Shenzhen 2000 11000000
4 Hunan Changsha 1000 9000000
5 Hubei Wuhan 900 8000000
6 Hubei Enshi 500 7000000
7 Guangdong Guangzhou 6000 12000000更新城市名重复的记录province为Jiangsu,city为Kunshan,Area为500,population为3000000
下面是错的:
update city_info set province='Jiangsu',city='Kunshan',Area=500,population=3000000 where city in (select city from city_info group by city having (count(*)) >1)
d sname(名字) smoney(业绩) sprovince(地区)
1 zhangsan 2098 A
2 lisi 3000 B
3 wangwu 6789 C
4 liumazi 4587 C
5 dongjiu 3298 B
6 shiga 4567 A显示出 业绩 大于同一地区平均值的 合同id、姓名、业绩和地区表b
id province city Area population
1 Guangdong Yangjiang 4000 4000000
2 Guangdong Guangzhou 6000 12000000
3 Guangdong Shenzhen 2000 11000000
4 Hunan Changsha 1000 9000000
5 Hubei Wuhan 900 8000000
6 Hubei Enshi 500 7000000
7 Guangdong Guangzhou 6000 12000000更新城市名重复的记录province为Jiangsu,city为Kunshan,Area为500,population为3000000
下面是错的:
update city_info set province='Jiangsu',city='Kunshan',Area=500,population=3000000 where city in (select city from city_info group by city having (count(*)) >1)
稀奇古怪的需求, 只要是城市名重复的记录,将所有这些重复记录全部更新为province=Jiangsu & city=Kunshan & Area=500 & population=3000000? 麻烦楼主 重新审视下你的需求吧,
from 表a t
Where smoney>(select avg(smoney) From 表a where sprovince=a.sprovince)
select * from a inner join (
select sprovince, avg(smoney) as smoney from a group by sprovince) b
on a.sprovince=b.sprovince
where a.smoney > b.smoney
1
select a.* from a inner join (select sprovince,avg(smoney) as nav from a group by sprovince) b
on a.sprovince=b.sprovince and a.smoney>b.nav
update 表b a inner join (select city from 表b group by city having count(*)>1) b on a.city=b.city
SET a.province='Jiangsu',a.city='Kunshan',a.Area=500,a.population=3000000
update city_info a inner join
(select province,city from city_info group by province,city having count(*) >1) b
on a.province=b.province and a.city=b.city
set a.province='Jiangsu',a.city='Kunshan',a.Area=500,a.population=3000000