MySQL 当月数据筛选条件
where DATE_FORMAT(created_at,'%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
postgresql 查询结果加上序号
ROW_NUMBER() OVER(ORDER BY real_name DESC) as num
sugar条件多条件选择 和时间范围选择
AND {xiao_qu in [conditions.multiSelect]}
AND {times [conditions.dateTimeRange]}
在PostgreSQL中可以直接对时间进行加减运算:
SELECT now()::timestamp + '1 year';
SELECT now()::timestamp + '1 month';
SELECT now()::timestamp + '1 day';
SELECT now()::timestamp + '1 hour';
SELECT now()::timestamp + '1 min';
SELECT now()::timestamp + '1 sec';
SELECT now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';
SELECT now()::timestamp + (col || ' day')::interval FROM table
select (current_date - interval '1 month')::date
select date_trunc('month',current_date)
select date_trunc('month',(current_date - interval '1 month'))::date
查询周几
- 查询周几
select '周'||extract(dow from current_date)
postgresql 中的除法/ 取整数, 1/4 = 0 如果需要小数位需要指定类型, ::numeric
select 8/(100-3) as c1,
round(8/(100-3) ,4) as c2,
round(8/(100-3)::numeric ,4) as c3,
8/(100-3)::numeric as c4
;
-[ RECORD 1 ]
c1 | 0
c2 | 0.0000
c3 | 0.0825
c4 | 0.08247422680412371134
CASE WHEN 条件判断
(CASE WHEN sum(b3.sum_baoming) IS NULL THEN 0
WHEN sum(b3.sum_baoming) IS NOT null THEN sum(b3.sum_goal)
END) as sales_group
年转换
EXTRACT(YEAR FROM created_at)=EXTRACT(YEAR FROM now())
判断为空用 IS NULL
转换数字 ::numeric
round((b1.pay_now-b2.pay_preview)::numeric/b2.pay_preview::numeric*100,2)
更新数据
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
清空表
TRUNCATE TABLE ke_userapply
连表更新
update staff_info
set is_leave = 1, endtime = current_date
from 员工数据
where staff_info."员工UserID" = 员工数据.员工userid
and staff_info.endtime = '2200-01-01'
and 员工数据.员工userid is null;
更新单表的某个字段
update staff_info
set endtime = '2200-01-01'
where endtime = '2199-12-31'
授予表权限
GRANT SELECT on "public".sales_counselor_statistics to smd_mm
显示表的所有字段
select admin.cfrom
显示当下表的所有字段
相当于select *