小貓貓大聯盟!幻與想的境界\(0w0)/

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;

複製代碼

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 LAT,LON from (select ID,COUNTRY,CITY,round(LAT,3) AS LAT,round(LON,3) AS LON FROM CITYTABLE group by round(LON,3),round(LAT,3)) as t1 order by CITY desc,ID limit 3,3;
select round(LAT,3) AS LAT,round(LON,3) AS LON
FROM CITYTABLE group by round(LON,3),round(LAT,3);
select round(LAT,3) AS LAT,round(LON,3) AS LON
FROM CITYTABLE group by round(LON,3),round(LAT,3) limit 5,15;

複製代碼