SQL
燕返zmzx
这个作者很懒,什么都没留下…
展开
-
postgreSQL 数组ZIP函数
需求 CREATE OR REPLACE FUNCTION “bi”.“zip”(“array1” anyarray, “array2” anyarray) RETURNS “pg_catalog”.“anyarray” AS $BODY$ SELECT ARRAY_AGG(ARRAY[a,b]) FROM unnest(array1,array2) x(a,b); $BODY$ LANGUAGE sql VOLATILE COST 100 版本PostgreSQL 9.4及以上 ...原创 2021-04-08 10:22:14 · 188 阅读 · 0 评论 -
PostgreSQL的generate_series()函数使用技巧
SELECT generate_series(1,12) AS month; SELECT generate_series(1,12,1) AS month; SELECT generate_series(‘2020-01-01’::DATE,‘2020-12-31’::DATE,‘1 month’::INTERVAL) AS first_of_month;原创 2020-12-15 10:44:56 · 1738 阅读 · 0 评论 -
SQL行转列通用办法
C1 C2 1 A 1 B 1 C || V C1 C2 C3 C4 1 A B C SELECT c1, MAX(CASE c2 WHEN ‘A’ THEN ‘A’) AS c2, MAX(CASE c2 WHEN ‘B’ THEN ‘B’) AS c3, MAX(CASE c2 WHEN ‘C’ THEN ‘C’) AS c4 FROM t1 ...原创 2019-03-11 18:50:46 · 311 阅读 · 0 评论 -
Postgresql通过查询进行更新
转自:https://bbs.csdn.net/topics/392389383 UPDATE tb1 SET score = COALESCE( b.scorea,0) from tb1 a left join tb2 b on a.ids=b.ida where a.ids = tb1.ids;原创 2019-03-26 19:08:12 · 4233 阅读 · 0 评论 -
连续区间聚合SQL写法
postgresql: with a as ( select 0 hour_index,'启' state union all select 1 hour_index,'停' state union all select 2 hour_index,'停' state union all select 3 hour_index,'启' state union all select ...原创 2019-03-24 11:42:11 · 1213 阅读 · 0 评论