PostgreSql 查询

1.PostgreSql 查询一个月内的天数

SELECT to_char( generate_series(date_trunc('month',now())+ interval '-1 month',
    date_trunc('month',now())+ interval '-1 day', '1 day'),'yyyy-mm-dd');
SELECT to_char( generate_series( to_timestamp((to_char((now() + interval '-1 month'), 'YYYY-MM-01')), 'YYYY-MM-DD 00:00:00'),    date_trunc('month',now())+ interval '-1 day', '1 day'),'yyyy-mm-dd');

-- 根据当前时间查询当月天数数据 毫秒时间戳需除以1000 转成北京时间
select date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8');
select date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8')+ interval '1 month -1 day';

select to_char( generate_series(date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8'),
 date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8')+ interval '1 month -1 day', '1 day'),'yyyy-mm-dd') days;

2.PG执行脚本

do
$$
    DECLARE
        unid   varchar;
        newId  varchar;
        date   timestamp;
        icount numeric;
        cur_list CURSOR FOR
            select id
            from f_and_tpm a
            where created_by = 'shw000';
    BEGIN
        date = now();
        OPEN cur_list;
        LOOP
            FETCH cur_list INTO unid;
            EXIT WHEN NOT FOUND;

            /*select count(1)
            into icount
            from f_and_tpm
            where id = unid;
            IF (icount > 1)
            THEN
                CONTINUE ;
            end if;*/

            newId = gen_random_uuid();

            update f_and_tpm
            set id=newId,
                created_time=date,
                --created_by='shw',
                updated_time=date,
                updated_by='shw'
            where id = unid;
            --插入数据
            INSERT INTO public.f_and_tpm_class_correlation (id, created_by, created_time, delete_flag, updated_by,
                                                            updated_time, equipment_id, remark, tpm_class_id, tpm_id)
            VALUES (gen_random_uuid(), 'shw000', date, 0, 'shw000',
                    date, null, null, 'ff8080818726933d0187272f21d902df',
                    newId);

        END LOOP;
        CLOSE cur_list;

    END;
$$

3.分组排序取最新数据

select * from (
select *,row_number() over (partition by code order by created_time desc) rt from f_sys_user) as t
where t.rt=1

4.PG删除多表关联数据(比较少用，记录下以便回忆)

delete from f_qms_process_quality a using  (
SELECT C.* FROM f_prm_product_route A ,f_mdm_factory B ,f_prm_route_step C
         WHERE A.inner_product_no='6D3' AND A.part_type='MC'
           AND A.org_id =B.ID  and b.CODE='DC'
 and c.route_id=a.unid  AND  a.delete_flag=0  AND  b.delete_flag=0   AND  c.delete_flag=0
) t where a.process_id=t.unid;

5.查询当前时间所在第几周

select 1 + date_part('week', dt) - date_part('week', dt - ((date_part('day', dt) - 1) || ' day')::interval)
from (values (now())) data(dt);

PG多表关联更新数据

 update f_weekly_maintenance a set serial_number=t.rn
 from (select id,to_char(week_start_date,'yyyymm')||'0'||row_number() over (partition by months order by weeks ) rn
       from f_weekly_maintenance ) as t
 where t.id= a.id