Sql 查询日记

1.point查询：SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;
2.point插入：INSERT INTO public.run_packinfo(the_geom) VALUES (ST_GeomFromText('POINT(26.074508 119.296494)',4326));
3.数据导入：INSERT into run_gpsinfo(devid,uploadtime,the_geom) select devid,uploadtime,st_geometryfromtext('POINT('||to_char(x,'999D9999')||' '||to_char(y,'999D9999')||')',4326)  
 from gpsinfo where x>100  LIMIT 50000 OFFSET 0
4.insert into run_packinfo(baledensity，uploadtime) select baledensity,iidspeed,baledatetime from baleinfo;
 
5.年：select to_char(uploadtime,'yyyy'),count(*) from run_packinfo group by to_char(uploadtime,'yyyy')
6.select to_char（exportDate，‘yyyy-mm’），sum（amount） from table1 group by to_char（exportDate，‘yyyy-mm’）


　　order