- drop database if exists CITYDATABASE;
- create database CITYDATABASE;
- use CITYDATABASE;
- drop table if exists CITYTABLE;
- create table CITYTABLE(ID numeric,COUNTRY varchar(50),CITY varchar(60),LAT numeric(11,8),LON numeric(11,8));
- load data infile 'C:/test/city.csv' into table CITYTABLE fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;
- SET SQL_SAFE_UPDATES=0;
- select ID,COUNTRY,CITY,round(LAT,1),round(LON,1) from CITYTABLE order by CITY,ID asc;
- select ID,COUNTRY,CITY,round(LAT,1),round(LON,1)
- from (select ID,COUNTRY,CITY,round(LAT,1),round(LON,1)FROM CITYTABLE group by round(LON,1),round(LAT,1))
- order by CITY,ID asc;
- select ID,COUNTRY,CITY,round(LAT,1),round(LON,1)
- FROM CITYTABLE group by round(LON,1),round(LAT,1)
- order by CITY,ID asc;
- select distinct CITY from CITYTABLE;
複製代碼 |