oracle:
- all_tab_columns
- all_tab_comments
- all_col_comments
- all_tables
- user_views
- all_views
select text from user_views WHERE view_name='view_table1';
select text from all_views WHERE view_name='view_table1';
1、根据表注释查询表名
select * from user_tab_comments where comments like '%指标说明%';
2、根据字段查询哪些表包含该字段
select table_name from DBA_TAB_COLUMNS where COLUMN_NAME='AVG_OUTLINE_DAYS';
3、查询当前数据库版本
select * from v$version;
4、查询视图组成结构。
select text from all_views WHERE view_name='视图名称';
5、去重数据(单纯distinct不可用)
方法一、
SELECT *
FROM (
SELECT
column1,
column2,
column3,
column8
ROW_NUMBER() OVER(PARTITION BY 去重字段 ORDER BY 非去重字段) ROW_N
FROM 表名
WHERE 1 = 1) T1
WHERE T1.ROW_N = 1;
方法二、
SELECT
column1,
column2,
column3,
column4
FROM (SELECT p.*,Row_number() over(partition by 去重字段 order by 非去重字段) as mm from 表名 p) s
where s.mm = 1;
6、需要多次查询表时,可以一次查询,需要在查询所有数据是进行行列转换。
select * from
(select tdate,acc_cd,acc_nm,index_value, index_cd from risk_dmapp.VIEW_R_RSK_MTR_PROD_IDX)
pivot(sum(index_value) for index_cd in('BOND_WT_CCT' BOND_WT_CCT,'BOND_ISSUER_TOP5_RATIO' BOND_ISSUER_TOP5_RATIO))
where acc_cd='TT1958' order by tdate
7、给用户添加同义词(例:risk_dm下视图V_R_RSK_MTR_ALTER_INV_EXPO建立一个同义词到risk_dmapp用户下)
CREATE SYNONYM risk_dmapp.V_R_RSK_MTR_ALTER_INV_EXPO FOR risk_dm.V_R_RSK_MTR_ALTER_INV_EXPO;
8、查询sequence和查询sequence的下一个取值
--查询sequence:
select * from ALL_SEQUENCES;
select * from USER_SEQUENCES where sequence_name = 'HIBERNATE_SEQUENCE';
--查询下一次取值:
SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM dual;
--oracle的hibernate默认sequence(也需要创建):
select * from USER_SEQUENCES where sequence_name = 'HIBERNATE_SEQUENCE';