Oracle常用查询技巧

Oracle常用查询技巧

---

将查询结果插入到表中

insert into 表A(id, name, age)
select sys_guid(), from 表B

chr(10) 回车换行符号 (可用于替换或拼接字符)

-- 拼接回车换行
'xxxxxx' || chr(10) || 'yyyyyyyy'
-- 替换回车换行符
replace(t.custName,chr(10),'<br/>')

合并查询 排序

-- 添加额外的字段 用于控制排序
select * from 
(    select
        1 sort_temp,
        xxxx 
    from x
union
    select
        2 sort_temp,
    xxxx from x
)  
order by 
sort_temp asc,
xx desc,
xx,asc 

根据指定维度 查询出最新的数

select * from (
    select row_number() over (partation by t.xx,t.xx order by t.xx_time desc) rn,t.*    
    from xxxxx t
    -- 记得条件在里面
    where 
        t.xxx = xxxx
) where rn = 1

查询所有的表

SELECT * FROM all_tables;

listagg 预先排序

SELECT 
    department_id,
    LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY hire_date) AS employees
FROM 
    (SELECT department_id, employee_name, hire_date 
     FROM employees 
     ORDER BY department_id, hire_date)
GROUP BY 
    department_id;