2021-02-20

最新推荐文章于 2021-12-20 21:15:28 发布

把每天当成生命中的最后一天

最新推荐文章于 2021-12-20 21:15:28 发布

阅读量194

点赞数

文章标签： oracle

本文链接：https://blog.csdn.net/L905128009/article/details/113877982

版权

包括列转行行转列的oracle的sql语句

1.excel按住ctrl键下拉的话显示的是上面的值,不按住的话显示的是+1的值

2.T_BAS_WATER_POINTINFO自动监测的城市关联的数据

SELECT MAX(FZTIME) FROM PWXK_BASE_ITEM_DATA WHERE FZTIME != 'null' AND FZTIME < GETDATE()
上面是salserver的对应的语句

imp TSUPERMONITOR/TSUPERMONITOR@orcl file=C:\Users\lenovo\Desktop\tsupermonitor.dmp fromuser=tsupermonitor touser=TSUPERMONITOR ignore=y

SELECT MAX(FZTIME) FROM PWXK.PWXK_BASE_ITEM_DATA WHERE FZTIME != 'null' AND FZTIME <= TO_CHAR(SYSDATE,'YYYY-MM-DD')

SELECT MAX(FZTIME) FROM [DBO].[PWXK_BASE_ITEM_DATA] WHERE FZTIME != 'null' AND FZTIME <= GETDATE()

1.sqlplus \nolog

2.conn sys/sys as sysdba

解决oracle的问题:

3.create spfile from pfile = 'E:\soft\oracle\admin\orcl\pfile\init.ora.713201715140'

SELECT * FROM TCODE.T_COD_POLLUTE

SELECT VAL,AA FROM TMAIN.T_ENV_MONI_WATER_EXCEL

UNPIVOT (

VAL FOR AA IN (

NVL(PH,'0'),NVL(DDL,'0')))

WITH AAA AS (

SELECT NVL(RJY,0)RJY,NVL(PH,0) PH,NVL(DDL,0) DDL FROM TMAIN.T_ENV_MONI_WATER_EXCEL

) SELECT * FROM AAA UNPIVOT (

VAL FOR AA IN (PH,DDL,RJY))

//行转列语句

WITH AAA AS (

SELECT NVL(RJY,0) RJY,NVL(PH,0) PH,NVL(DDL,0) DDL FROM TMAIN.T_ENV_MONI_WATER_EXCEL

) SELECT * FROM AAA UNPIVOT (

VAL FOR AA IN (PH,DDL,RJY))

select * from tenv.T_ENV_MONI_WATERPOINTDATA_M

ALTER TABLE TMAIN.CESHI ADD GE_1 VARCHAR2(100)

2016.5-2017.6典型乡镇（乡镇饮用水）

INSERT INTO TENV.T_ENV_MONI_WATERPOINTDATA_M

(PKID,MONITORTIME,CSMC,CSDM,SYDMC,SSSX,SYDXZ,POLLUTEVALUE,POLLUTEAVGVALUE)

SELECT SYS_GUID() PKID,TO_DATE(A.CYSJ,'YYYY-MM-DD') MONITORTIME,A.CSMC,A.CSDM,A.SYDMC,

A.SSSX,A.SYDXZ,VAL POLLUTEVALUE,VAL POLLUTEAVGVALUE

FROM (

WITH AAA AS (

SELECT

nvl(SW , 0) SW,

nvl(PH , 0) PH,

nvl(RJY , 0) RJY,

nvl(GMSJZS , 0) GMSJZS,

nvl(HXXYL , 0) HXXYL,

nvl(WRSHXYL , 0) WRSHXYL,

nvl(AD , 0) AD,

nvl(ZL , 0) ZL,

nvl(ZD , 0) ZD,

nvl(TONG , 0) TONG,

nvl(XIN , 0) XIN,

nvl(YHF , 0) YHF,

nvl(XI , 0) XI,

nvl(SHEN , 0) SHEN,

nvl(GONG , 0) GONG,

nvl(GE , 0) GE,

nvl(LIUJIAGE , 0) LIUJIAGE,

nvl(QIAN , 0) QIAN,

nvl(QHW , 0) QHW,

nvl(HFF , 0) HFF,

nvl(SYL , 0) SYL,

nvl(YLZBMHXJ , 0) YLZBMHXJ,

nvl(LIUHW , 0) LIUHW,

nvl(FDCJQ , 0) FDCJQ,

nvl(LSY , 0) LSY,

nvl(LVHW , 0) LVHW,

nvl(XSY , 0) XSY,

nvl(TIE , 0) TIE,

nvl(MENG , 0) MENG,

nvl(SLJW , 0) SLJW,

nvl(SLHT , 0) SLHT,

nvl(SXJW , 0) SXJW,

nvl(ERLVJW , 0) ERLVJW,

nvl(YIERERLVYW , 0) YIERERLVYW,

nvl(HYLBW , 0) HYLBW,

nvl(LYX , 0) LYX,

nvl(YIYIERLVYIXI , 0) YIYIERLVYIXI,

nvl(YIERERLVYIXI , 0) YIERERLVYIXI,

nvl(SANLVYIXI , 0) SANLVYIXI,

nvl(SILVYIXI , 0) SILVYIXI,

nvl(LVDINGERXI , 0) LVDINGERXI,

nvl(LIULVDINGERXI , 0) LIULVDINGERXI,

nvl(BENYIXI , 0) BENYIXI,

nvl(JIAQUAN , 0) JIAQUAN ,

nvl(YIQUAN , 0) YIQUAN ,

nvl(BINGXIQUAN , 0) BINGXIQUAN ,

nvl(SANLVYIQUAN , 0) SANLVYIQUAN ,

nvl(BEN , 0) BEN ,

nvl(JIABEN , 0) JIABEN ,

nvl(YIBEN , 0) YIBEN ,

nvl(ERJIABENYI , 0) ERJIABENYI ,

nvl(YIBINGBEN , 0) YIBINGBEN ,

nvl(LVBEN , 0) LVBEN ,

nvl(YIERERLVBEN , 0) YIERERLVBEN ,

nvl(YISIERLVBEN , 0) YISIERLVBEN ,

nvl(SLBER , 0) SLBER ,

nvl( SLBSAN , 0) SLBSAN ,

nvl(LIULVBEN , 0) LIULVBEN ,

nvl(XIAOJIBEN , 0) XIAOJIBEN ,

nvl(ERXIAOJIBENSI , 0) ERXIAOJIBENSI ,

nvl(ERSIERXIAOJIJIABEN , 0) ERSIERXIAOJIJIABEN ,

nvl(ERSILIUSANXIAOJIJIABEN , 0) ERSILIUSANXIAOJIJIABEN ,

nvl(XJLBW , 0) XJLBW ,

nvl(ERSIERXIAOJILVBEN , 0) ERSIERXIAOJILVBEN ,

nvl(ERSIERLVBENFEN , 0) ERSIERLVBENFEN ,

nvl(ERSILIUSANLVBENFEN , 0) ERSILIUSANLVBENFEN ,

nvl(WULVFEN , 0) WULVFEN ,

nvl(BENAN , 0) BENAN ,

nvl(BINGXIXIANAN , 0) BINGXIXIANAN ,

nvl(BINGXIJING , 0) BINGXIJING ,

nvl(LBEJSEDZ , 0) LBEJSEDZ ,

nvl(LBEJSEEYJJJZ , 0) LBEJSEEYJJJZ ,

nvl(SHUIHEJING , 0) SHUIHEJING ,

nvl(SIYIJIQIAN , 0) SIYIJIQIAN ,

nvl(BIDING , 0) BIDING ,

nvl(SONGJIEYOU , 0) SONGJIEYOU ,

nvl(KUWEISUAN , 0) KUWEISUAN ,

nvl(DJHYS , 0) DJHYS ,

nvl(HXL , 0) HXL ,

nvl(DDT , 0) DDT ,

nvl(LD , 0) LD ,

nvl(HYQL , 0) HYQL ,

nvl(DLL , 0) DLL ,

nvl(JJDLL , 0) JJDLL ,

nvl(MLLL , 0) MLLL ,

nvl(LG , 0) LG ,

nvl(DDW , 0) DDW ,

nvl(DBC , 0) DBC ,

nvl(NXL , 0) NXL ,

nvl(BJQ , 0) BJQ ,

nvl(JNW , 0) JNW ,

nvl(XIUQINGJUZHI , 0) XIUQINGJUZHI ,

nvl(ATELAJIN , 0) ATELAJIN ,

nvl(BENBINGBI , 0) BENBINGBI ,

nvl(JIAJIGONG , 0) JIAJIGONG ,

nvl(DUOLVLIANBENLIU , 0) DUOLVLIANBENLIU ,

nvl(WEINANGZAODUSU , 0) WEINANGZAODUSU ,

nvl(HUANGLIN , 0) HUANGLIN ,

nvl(MU , 0) MU ,

nvl(GU , 0) GU ,

nvl(PI , 0) PI ,

nvl(PENG , 0) PENG ,

nvl(TI , 0) TI ,

nvl(NIE , 0) NIE ,

nvl(BEI , 0) BEI ,

nvl(FAN , 0) FAN ,

nvl(TAI , 0) TAI ,

nvl(TUO , 0) TUO ,

nvl(TMD , 0) TMD ,

nvl(YLSA , 0) YLSA ,

nvl(XUANFUWU , 0) XUANFUWU ,

nvl(DDL , 0) DDL ,

CSMC,CSDM,SYDMC,SSSX,SYDXZ,CYSJ

FROM TMAIN.ZSSDXXZJZSSHYYS

) SELECT * FROM AAA

UNPIVOT ( VAL FOR AA IN (

SW ,

PH ,

RJY ,

GMSJZS ,

HXXYL ,

WRSHXYL ,

AD ,

ZL ,

ZD ,

TONG ,

XIN ,

YHF ,

XI ,

SHEN ,

GONG ,

GE ,

LIUJIAGE ,

QIAN ,

QHW ,

HFF ,

SYL ,

YLZBMHXJ ,

LIUHW ,

FDCJQ ,

LSY ,

LVHW ,

XSY ,

TIE ,

MENG ,

SLJW ,

SLHT ,

SXJW ,

ERLVJW ,

YIERERLVYW ,

HYLBW ,

LYX ,

YIYIERLVYIXI ,

YIERERLVYIXI ,

SANLVYIXI ,

SILVYIXI ,

LVDINGERXI ,

LIULVDINGERXI ,

BENYIXI ,

JIAQUAN ,

YIQUAN ,

BINGXIQUAN ,

SANLVYIQUAN ,

BEN ,

JIABEN ,

YIBEN ,

ERJIABENYI ,

YIBINGBEN ,

LVBEN ,

YIERERLVBEN ,

YISIERLVBEN ,

SLBER ,

SLBSAN ,

LIULVBEN ,

XIAOJIBEN ,

ERXIAOJIBENSI ,

ERSIERXIAOJIJIABEN ,

ERSILIUSANXIAOJIJIABEN ,

XJLBW ,

ERSIERXIAOJILVBEN ,

ERSIERLVBENFEN ,

ERSILIUSANLVBENFEN ,

WULVFEN ,

BENAN ,

BINGXIXIANAN ,

BINGXIJING ,

LBEJSEDZ ,

LBEJSEEYJJJZ ,

SHUIHEJING ,

SIYIJIQIAN ,

BIDING ,

SONGJIEYOU ,

KUWEISUAN ,

DJHYS ,

HXL ,

DDT ,

LD ,

HYQL ,

DLL ,

JJDLL ,

MLLL ,

LG ,

DDW ,

DBC ,

NXL ,

BJQ ,

JNW ,

XIUQINGJUZHI ,

ATELAJIN ,

BENBINGBI ,

JIAJIGONG ,

DUOLVLIANBENLIU ,

WEINANGZAODUSU ,

HUANGLIN ,

MU ,

GU ,

PI ,

PENG ,

TI ,

NIE ,

BEI ,

FAN ,

TAI ,

TUO ,

TMD ,

YLSA ,

XUANFUWU ,

DDL

))) A INNER JOIN TMAIN.T_ENV_MONI_WATER_MAPPER B

ON A.AA = B.NAME

ALTER TABLE TMAIN.JAHY20162017 ADD SEDU VARCHAR2(100)

ALTER TABLE TMAIN.JAHY20162017 ADD ZHUODU VARCHAR2(100)