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 by to_char（exportDate，‘yyyy-mm’）;
7.select to_char（exportDate，‘yyyy-Q’），sum（amount） from table1 group by to_char（exportDate，‘yyyy-Q’）


　　order by to_char（exportDate，‘yyyy-Q’）;
8.select to_char（exportDate，‘yyyy-IW’），sum（amount） from table1 group by to_char（exportDate，‘yyyy-IW’）


　　order by to_char（exportDate，‘yyyy-IW’）;




今天


select * from 表名 where to_days(时间字段名) = to_days(now());


昨天


SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1


7天


SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)


近30天


SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)


本月


SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )


上一月


SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1