免費論壇 繁體 | 簡體
Sclub交友聊天~加入聊天室當版主
分享
返回列表 發帖

city-2

  1. drop database if exists CITYDATABASE;
  2. create database CITYDATABASE;
  3. use CITYDATABASE;
  4. drop table if exists CITYTABLE;
  5. create table CITYTABLE(ID numeric,COUNTRY varchar(50),CITY varchar(60),LAT numeric(11,8),LON numeric(11,8));
  6. load data infile 'C:/test/city.csv' into table CITYTABLE fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;
  7. SET SQL_SAFE_UPDATES=0;

  8. select ID,COUNTRY,CITY,round(LAT,1),round(LON,1) from CITYTABLE order by CITY,ID asc;
  9. select ID,COUNTRY,CITY,round(LAT,1),round(LON,1)
  10. from (select ID,COUNTRY,CITY,round(LAT,1),round(LON,1)FROM CITYTABLE group by round(LON,1),round(LAT,1))
  11. order by CITY,ID asc;

  12. select ID,COUNTRY,CITY,round(LAT,1),round(LON,1)
  13. FROM CITYTABLE group by round(LON,1),round(LAT,1)
  14. order by CITY,ID asc;

  15. select distinct CITY from CITYTABLE;
複製代碼
分享到: QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友

小貓貓2017了喔!
(點一下康娜醬傳送到小貓貓2017大事記)

  1. drop database if exists CITYDATABASE;
  2. create database CITYDATABASE;
  3. use CITYDATABASE;
  4. drop table if exists CITYTABLE;
  5. create table CITYTABLE(ID numeric,COUNTRY varchar(50),CITY varchar(60),LAT numeric(11,8),LON numeric(11,8));
  6. load data infile 'C:/test/city.csv' into table CITYTABLE
  7. fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;
  8. SET SQL_SAFE_UPDATES=0;

  9. 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;

  10. select round(LAT,3) AS LAT,round(LON,3) AS LON
  11. FROM CITYTABLE group by round(LON,3),round(LAT,3);

  12. select round(LAT,3) AS LAT,round(LON,3) AS LON
  13. FROM CITYTABLE group by round(LON,3),round(LAT,3) limit 5,15;
複製代碼

小貓貓2017了喔!
(點一下康娜醬傳送到小貓貓2017大事記)

TOP

返回列表