获取orcale数据库中某张表的所有字段名、某个字段名、将某个字段名用别名表示并查询数据、某些字段名，给到查询结果

1、获取某张表的所有字段名

select column_name from all_tab_columns 
where table_name='YX_JMDDL'

查询结果如上图所示，用于查询某张表中所有字段

2、知道表中某个字段，需要把该字段在查询结果中显示时：

select column_name from 
(select column_name from all_tab_columns where table_name='YX_JMDDL') 
where column_name='CONS_NO'

查询结果如下图2所示需要把字段名CONS_NO在查询结果中显示出来

3、需要查询表中某些个字段的数据，返回查询结果时想用别名表示该字段名时：

SELECT T.id as ydxxId, T.cons_no as Value1 
FROM YYJC_FX.YX_JMDDL T 
WHERE T.YD_STATUS IS NULL  AND NVL(T.IS_BMD, 2) IN (0,2) 
and to_date(cycle,'yyyyMM') >=to_date('201904' ,'yyyyMM') 
and to_date(cycle,'yyyyMM') <=to_date('202104' ,'yyyyMM') 
and  dsbm= '61401'

查询结果如下图3所示，id字段名的别名为ydxxId、cons_no字段名的别名为Value1

4、可以将某个字段的数据查出用别名显示，并把该字段名用别名显示在该列数据之前，sql如下：

SELECT T.id as ydxxId, 
(select column_name from 
(select column_name from all_tab_columns where table_name='YX_JMDDL') 
where column_name='CONS_NO') 
as key1 ,T.cons_no as value1 
FROM YYJC_FX.YX_JMDDL T 
WHERE T.YD_STATUS IS NULL  AND NVL(T.IS_BMD, 2) IN (0,2) 
and to_date(cycle,'yyyyMM') >=to_date('201904' ,'yyyyMM') 
and to_date(cycle,'yyyyMM') <=to_date('202104' ,'yyyyMM') 
and  dsbm= '61401'

如下图4所示为该sql语句查询结果，id别名为ydxxId，column_name 别名为key1,表示字段名，cons_no别名为value1，表示该字段下对映数据

5、升级版的第4步，查询多个字段

select T.id as ID,
(select column_name from (select column_name from all_tab_columns 
where table_name='YX_CTDL') where column_name='TG_ID') as fileKay1 ,
T.TG_ID as fileValue1 ,
(select column_name from (select column_name from all_tab_columns 
where table_name='YX_CTDL') where column_name='MP_NO') as fileKay2 , 
T.MP_NO as fileValue2 ,
(select column_name from (select column_name from all_tab_columns 
where table_name='YX_CTDL') where column_name='METER_ID') as fileKay3 , 
T.METER_ID as fileValue3 
FROM YYJC_FX.YX_CTDL T 
WHERE T.YD_STATUS IS NULL  AND NVL(T.IS_BMD, 2) IN (0,2) 
and  dsbm= '61401' order by cycle desc

如下图5所示为id和三个字段数据及各字段名的查询结果显示