sql

pgsql

筛选和当前年份匹配的记录

user 表，根据birthday筛选

select * from user where extract(year from birthday)=extract(year from now());

--当前年
extract(year from now())

/**
* 筛选时间为本年，相同id同一天多条算做一条
/
SELECT count() from (\n" +
“SELECT *,row_number() OVER(partition by id order by id desc) rn FROM user)a\n” +
"where rn=1 and extract(year from birthday)=extract(year from now());

每天最新的一条数据

select * from monitor a,
(SELECT max(create_time) rq
FROM monitor  
GROUP BY to_char(create_time,'YYYY-MM-DD'),name) b
where a.create_time=b.rq

//pgsql 输出json

with t as (
SELECT y1.gdssc_zyq as factoryName,sum(y1.cd) as cd,y1.gdfl as gdfl FROM raw_data.ytgd001 y1 
WHERE  gdssc_zyq IS NOT NULL
GROUP BY y1.gdssc_zyq,y1.gdfl
),t2 as (
select factoryName
,max(case gdfl when 'I类' then cd end  ) oneLength
,max(case gdfl when 'II类' then cd end  ) twoLength
,max(case gdfl when 'III类' then cd end  ) threeLength
from t
group by factoryName
),t3 as (
SELECT row_to_json(t2) cc,factoryName FROM t2
)
SELECT '['||string_agg(cc::text,','  ORDER BY position(factoryName  in  '采油一厂采油二厂采油三厂采油四厂采油五厂采油六厂') )||']'
from t3

[{"factoryname":"采油一厂","onelength":193.00,"twolength":60.30,"threelength":140.00},{"factoryname":"采油二厂","onelength":80.02,"twolength":null,"threelength":61.00},{"factoryname":"采油三厂","onelength":30.00,"twolength":null,"threelength":20.00},{"factoryname":"采油四厂","onelength":null,"twolength":null,"threelength":163.00}]

--自动为nunll时返回0
select COALESCE(b.price, 0) as price from fruit_sale b

pg:设备历史表最后一条
with a as (
SELECT *,row_number() OVER(partition by dxid order by cjsj desc) rn FROM raw_data.dbbc011_analysis
) SELECT * FROM a WHERE rn = 1

--pg: 查询一周前的日期



with a as (
SELECT *,row_number() OVER(partition by dxid order by cjsj desc) rn 
FROM raw_data.dbbc011_analysis WHERE 
cjsj='2020-01-06' and
-- dwdm >='0101' and dwdm<='0104'
--dwdm in ('0101','0102','0103','0104')
--dwdm like '01%' and
--dwdm like '%01'
 1=1  and dwdm like '06%'
--${if(len(单位名称) == 0,"","and dwmc = '" + 单位名称 + "'")}
) 
SELECT *,
 case gzzt WHEN '1' THEN '生产'
else '停产' END as gzzt_n
FROM a 
WHERE rn = 1 
ORDER BY dxmc desc








--当前日期
select current_date ;

--当前时间
SELECT now();
select date_trunc('day',now());

--格式化当前时间
select now()::timestamp(0)without time zone;


--当前时间
SELECT to_char(now(),'yyyy-MM-dd HH24:MI:SS') 

--  -interval '1d' 向前推一天
select date_trunc('day',now()) -interval '1d';


--向前推6小时
select date_trunc('sec',now())-interval '6 hours';

--  -interval '1d'+interval '6 hours'; 向前推1天，并加6小时
select date_trunc('sec',now()) -interval '1d'+interval '6 hours';


时间的计算方式，如下
 select now() + interval '10 min/year/month/day/hour/sec/ (1 year 1 month 1 day 1 hour 1 min 1 sec)'