目录1、用SQL查询最近N天的数据2、用SQL语句实现替换字段里的内容3、SQL将查询结果赋值4、SQL查询字段内容为 NULL值时候赋值总结
1、用SQL查询最近N天的数据
思路：先获取当前的时间，然后更具当前时间去减去对应的天数，就可以得到需要的天数了。然后用这些天数作为条件，然后即可查询出需要多少天的数据了。
— 获取当前时间SELECT CURRENT_TIMESTAMP;– 或SELECT now();select CURRENT_TIMESTAMP :: DATE AS \”当前日期\”;SELECT CURRENT_DATE;select CURRENT_TIMESTAMP :: TIME AS \”当前时间\”;SELECT CURRENT_TIME;
最近N天
— 最近7天SELECT DATE( T ) AS DAYFROMgenerate_series ( ( CURRENT_TIMESTAMP – INTERVAL \’6 day\’ ) :: DATE,CURRENT_TIMESTAMP :: DATE, \’1 days\’ ) AS T ;
当然6 day、1 day这些也是可修改的；
–统计最近30、7、昨天、今天的数据SELECTcount(*) AS AllData,count(*) filter ( WHERE create_time BETWEEN ( SELECT CURRENT_DATE – INTERVAL \’30 day\’ ) AND CURRENT_DATE ) AS Last30DayData,count(*) filter ( WHERE create_time BETWEEN ( SELECT CURRENT_DATE – INTERVAL \’7 day\’ ) AND CURRENT_DATE ) AS Last7DayData,count(*) filter ( WHERE create_time BETWEEN ( SELECT CURRENT_DATE – INTERVAL \’1 day\’ ) AND CURRENT_DATE ) AS YesterdayData,count(*) filter ( WHERE create_time >= CURRENT_DATE ) AS todayAddData FROMtable_name;
实例如下：查询lic_plate字段不为空和为空的最近7天的数据
— 查询lic_plate字段不为空的最近7天的数据select count(tn.lic_plate) AS number,tn.create_time AS percentagefrom table_name tnwhere tn.create_time in (SELECT DATE(T) AS DAY FROM generate_series((CURRENT_TIMESTAMP – INTERVAL \’6 day\’) :: DATE, CURRENT_TIMESTAMP :: DATE,\’1 days\’) AS T) AND lic_plate is not nullgroup by tn.create_time order by tn.create_time; — 查询lic_plate字段为空的最近7天的数据select count(id) AS number,CSQV.create_time AS percentagefrom (select * from table_name tn where lic_plate is null) AS CSQV where CSQV.create_time in (SELECT DATE(T) AS DAY FROM generate_series((CURRENT_TIMESTAMP – INTERVAL \’6 day\’) :: DATE, CURRENT_TIMESTAMP :: DATE,\’1 days\’) AS T)group by CSQV.create_time;
2、用SQL语句实现替换字段里的内容
思路：使用替换函数替换即可
— 替换语句update 表名 set 替换字段名 = replace(替换字段名,\’被替换内容\’,\’替换内容\’);
实例如下：将 my_test 表的 test_content字段内容进行替换
update my_test set test_content = replace(test_content,\’1\’,\’18\’);
替换函数：如果没有可以创建，创建函数如下：
— replace all occurrences in string of old_substr with new_substr — auto-generated definitioncreate function replace(text, text, text) returns text immutable strict cost 1 language internalas$$begin– missing source codeend;$$; comment on function replace(text, text, text) is \’replace all occurrences in string of old_substr with new_substr\’;
3、SQL将查询结果赋值–查询表数据，给test_content字段并赋值select test_id,test_code,\’测试数据\’ test_content from my_test;–查询表数据给自定义 ABCD字段 并赋值select test_id,test_code,\’测试数据\’ ABCD from my_test;
4、SQL查询字段内容为 NULL值时候赋值select test_id,test_code,COALESCE(test_content,\’测试数据\’) ABCD from my_test;– ||select test_id,test_code, CASE WHEN test_content IS NOT NULL THEN test_content ELSE \’测试数据\’ END ABCD from my_test;
总结
到此这篇关于PGSQL查询最近N天的数据及SQL语句实现替换字段内容的文章就介绍到这了,更多相关PGSQL查询最近N天数据内容请搜索悠久资源以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源！
