oracle常用查询记录

下面记录了一些平时遇到的查询语句：

1.级联查询

select *from (表名)start with (根字段)(条件) --例如：root_field in (#{查询条件})connect by prior (子节点字段) = (父节点字段)

向上递归时只需将连接条件改为：（父节点字段）=（子节点字段）

2.分页查询

select *from (select t.*, rownum as r from (表名) t where rownum <= #{a})where r>#{b}

传参时：
int a = currPage*pageSize;
int b = (currPage-1)*pageSize;

3.导出表结构到Excel

查询表结构语句

SELECT t1.Table_Name AS \"表名称\",t3.comments AS \"表说明\",t1.Column_Name AS \"字段名称\",t1.DATA_TYPE || \'(\' || t1.DATA_LENGTH || \')\' AS \"数据类型\",t1.NullAble AS \"是否为空\",t2.Comments AS \"字段说明\",t1.Data_Default AS \"默认值\",t4.created AS \"建表时间\"FROM cols t1LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_nameAND t1.Column_Name = t2.Column_NameLEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_nameLEFT JOIN user_objects t4 ON t1.table_name = t4.OBJECT_NAMEWHERE NOT EXISTS(SELECT t4.Object_NameFROM User_objects t4WHERE t4.Object_Type = \'TABLE\'AND t4.Temporary = \'Y\'AND t4.Object_Name = t1.Table_Name)order by t1.TABLE_NAME, t1.COLUMN_ID

使用pl/sql在查询结果上方点击“导出查询结果”，选择导出HTML文件，更改文件后缀名为.xls，即可保存表数据到Excel中。或者直接保存为csv格式。

4.查询指定年指定月的每一天

其中年份和月份’202002’是变量，

SELECT TRUNC(to_date(\'202002\', \'yyyyMM\'), \'MM\') + ROWNUM - 1 as dayFROM DUALCONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(to_date(\'202002\', \'yyyyMM\')), \'dd\'))

5.查询当前年份每一天

其中年份’2020’是变量，

SELECT TRUNC(to_date(\'2020\', \'yyyy\'), \'YYYY\') + ROWNUM - 1 as dayFROM DUALCONNECT BY ROWNUM <= ADD_MONTHS(TRUNC(to_date(\'2020\', \'yyyy\'), \'YYYY\'), 12) - TRUNC(to_date(\'2020\', \'yyyy\'), \'YYYY\')

持续更新中…