oracle sql

1.连表查询，格式化日期并重命名

select t0.*, t1.ym from (select * FROM proj_workhour where center=‘测试’ and workdate BETWEEN TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) and TO_DATE(‘2024-08-31’, ‘YYYY-MM-DD’) order by workdate desc) t0
left join
(select id, to_char(workdate, ‘YYYYMM’) as ym FROM proj_workhour where center=‘测试’ and workdate BETWEEN TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) and TO_DATE(‘2024-08-31’, ‘YYYY-MM-DD’) order by workdate desc) t1
on t0.id=t1.id;

2.模糊查询

SELECT * FROM qms_usestate WHERE soft LIKE CONCAT(‘%’, concat(‘865’, ‘%’)) ORDER BY state_date DESC;

3.日期转换成周历，即当年第几周

select id, to_char(workdate, ‘YYYYMM’) as month, TO_CHAR(workdate, ‘IW’) AS week FROM proj_workhour；