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
阅读更多
个人分类: 一般日志
上一篇mybtis多参数传递
下一篇ssm,ssh全配置
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭