想清下数据库中的脏数据,A表存放属性,B表存放位置,但是上传的位置如果是从地图上取位置,但是地图没有做居中操作的话,位置坐标在原点取值,属于错误数据,查查发现delete只能对某张表中的数据进行删除,所以分两步:
1、删除A表中的问题属性数据:
delete from A where ID in (select ID from B where charindex('121',Lon) = 0 and charindex('31',Lat)=0 )
//把varchar转成float查找某个经纬度范围内的数据
delete from A where ID in (select ID from B where Convert(float,Longitude) between 100 and 130
and Convert(float, Latitude) between 20 and 40 )
2、删除B表中的位置数据:
delete from B where charindex('121',Lon) = 0 and charindex('31',Lat)= 0
delete from B where Convert(float,Longitude) between 100 and 130
and Convert(float, Latitude) between 20 and 40