2021-02-20

一些重要的sql语句

插入数据的sql语句:

INSERT INTO TMAIN.JDP_EFORM_EXCELSHEETS

SELECT '11' DESIGERID,'近岸海域' DESIGERNAME,'JAHY' EXCELNAME,'Sheet1' SHEETNAME,'1' STARTROW,

'' TABLEDATASOURCE,'' BEFORERUNSQL,'TENV.T_JAHY' TABLENAME,'' AFTERRUNSQL

FROM DUAL

 

//删除表中的重复记录,保留ROWID最小的那一个

DELETE FROM TMAIN.T_ENV_MONI_WATER_MAPPER A WHERE (A.CODE,A.NAME) IN (SELECT CODE,NAME FROM

TMAIN.T_ENV_MONI_WATER_MAPPER  GROUP BY CODE,NAME HAVING COUNT(*) > 1)

AND ROWID NOT IN (SELECT MIN(ROWID) FROM  TMAIN.T_ENV_MONI_WATER_MAPPER A GROUP BY CODE,NAME HAVING COUNT(*)>1)

 

ALTER TABLE TENV.T_JAHY ADD YLS VARCHAR2(100)

ALTER TABLE TENV.T_CJSKSJTJ ADD PKID VARCHAR2(100)

ALTER TABLE TENV.T_CJSKSJTJ ADD PKID CLOB

 

ALTER TABLE 表名 DROP COLUMN 字段名(即列名);

 

//更新语句

UPDATE TMAIN.JDP_EFORM_EXCELFIELDS  A SET A.SHEETCOLDESCR = (

SELECT POLLUTENAME FROM  TMAIN.T_ENV_MONI_WATER_MAPPER B WHERE A.FIELDCODE = B.NAME AND ROWNUM =1

) WHERE A.DESIGERID = 11

AND EXISTS (SELECT 1 FROM TMAIN.T_ENV_MONI_WATER_MAPPER B WHERE A.FIELDCODE = B.NAME AND ROWNUM =1)

 

UPDATE TMAIN.JDP_EFORM_EXCELFIELDS  SET SHEETCOLNUM = (ROWNUM - 1)  WHERE DESIGERID = 9

 

INSERT INTO  TMAIN.T_ENV_MONI_WATER_MAPPER VALUES ('A25045','BENBINGPI','苯并芘')

 

//51数据库上面的表,更新成了对应的名称

SELECT * FROM  DSJYDD.T_BAS_WATER_POINTINFO

 

 

UPDATE TMAIN.JDP_EFORM_EXCELFIELDS A SET A.FIELDCODE = trim(A.FIELDCODE) WHERE DESIGERID = 14

 

 

 

INSERT INTO TMAIN.JDP_EFORM_EXCELFIELDS

SELECT SYS_GUID() SHEETCOLPKID,'67' SHEETCOLNUM,'PKID' SHEETCOLDESCR,'PKID' FIELDCODE,'uuid' FIELDDEFAULTVALUE,

'' CHECKMETHOD,'' CHECKMETHODDESCR,'15' DESIGERID,'' FIELDTYPE,'' INTRANSFORM,'' OUTTRANSFORM

FROM DUAL

 

 

 

UPDATE TMAIN.T_ENV_MONI_WATER_MAPPER A SET A.CODE = (

SELECT CODE FROM  TCODE.T_COD_POLLUTE B WHERE A.POLLUTENAME = B.CNAME

AND B.CODE LIKE 'W%'

--AND ROWNUM =1

)

WHERE A.CODE LIKE 'A%' AND EXISTS (SELECT 1 FROM TCODE.T_COD_POLLUTE B WHERE A.POLLUTENAME = B.CNAME

AND B.CODE LIKE 'W%')

 

imp tcode/tcode@orcl file=D:\work\silu\tcode.dmp  fromuser=tcode touser=tcode tables=t_cod_region ignore=y

 

imp tenv/tenv@orcl file=D:\work\silu\tenv.dmp  fromuser=tenv touser=tenv tables=T_ENV_MONI_WATERPOINTDATA_M ignore=y

 

UPDATE TENV.T_ENV_MONI_WATERPOINTDATA_M SET POINTCODE = NULL

 

SELECT DISTINCT POINTCODE,POINTNAME FROM TENV.T_ENV_MONI_WATERPOINTDATA_M

---

在PLSQLDEVELOPER中定义更新字段超过4000个字符串的值是下面的值得方式:

//乡镇的sql文件:

DECLARE  

  clobValue TMAIN.JDP_EFORM_EXCELSHEETS.AFTERRUNSQL%TYPE;  

BEGIN  

  clobValue := 'INSERT INTO TENV.T_ENV_MONI_WATERPOINTDATA_M (PKID,MONITORTIME,POLLUTEVALUE,POLLUTEAVGVALUE,CODE_POLLUTE,POLLUTENAME,POINTCODE,POINTNAME) SELECT SYS_GUID() PKID,TO_DATE(A.MONITORTIME,''YYYY-MM-DD HH24:MI:SS'') MONITORTIME,VAL POLLUTEVALUE,VAL POLLUTEAVGVALUE,B.CODE CODE_POLLUTE,B.POLLUTENAME POLLUTENAME,SYDDM,SYDMC FROM (WITH AAA AS (SELECT NVL(BYQSL,0) BYQSL,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(LIANBENAN,0) LIANBENAN,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(ZAOMIDU,0) ZAOMIDU,NVL(SW,0) SW,NVL(DDL,0) DDL,NVL(XUANFUWU,0) XUANFUWU,SYDDM,SYDMC,CYSJ MONITORTIME FROM TENV.T_ZSSDXXZJZSSHYYSSYDSZJCSJBB) SELECT * FROM AAA UNPIVOT (VAL FOR AA IN (BYQSL,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,LIANBENAN,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,ZAOMIDU,SW,DDL,XUANFUWU))) A INNER JOIN TMAIN.T_ENV_MONI_WATER_MAPPER B ON A.AA = B.NAME;UPDATE TENV.T_ENV_MONI_WATERPOINTDATA_M A SET A.POINTCODE = (SELECT POINTCODE FROM TENV.T_ENV_MONI_WATERPOINTINFO B WHERE B.CODE_WSYSTEMTYPE = ''xzyys'' AND B.MONITORTYPE = 1 AND A.POINTNAME = B.POINTNAME AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM TENV.T_ENV_MONI_WATERPOINTINFO B WHERE B.CODE_WSYSTEMTYPE = ''xzyys'' AND B.MONITORTYPE = 1 AND ROWNUM = 1) AND A.POINTCODE IS NULL';

  UPDATE TMAIN.JDP_EFORM_EXCELSHEETS T SET T.AFTERRUNSQL = clobValue WHERE DESIGERID = 10;  

  COMMIT;  

END;

---

DECLARE

clobValue 表名.字段名%TYPE;

BEGIN

clobValue := 'XXX'; --字段内容

UPDATE 表名 T SET T.字段名 = clobValue WHERE 条件;

COMMIT;

END;

---

ALTER TABLE TMAIN.JDP_EFORM_EXCELSHEETS A DROP COLUMN AFTERRUNSQL

 

ALTER TABLE TMAIN.JDP_EFORM_EXCELSHEETS ADD AFTERRUNSQL2 VARCHAR2(4000);

UPDATE  TMAIN.JDP_EFORM_EXCELSHEETS SET AFTERRUNSQL1 = AFTERRUNSQL2;

UPDATE TMAIN.JDP_EFORM_EXCELSHEETS  SET AFTERRUNSQL2 = AFTERRUNSQL

---

 

UPDATE TENV.T_ENV_MONI_WATERPOINTINFO SET WQDATASOURCETYPENAME = '长江水库' ,CODE_WSYSTEMTYPE = 'cjsk',WATERCODE=8,WATERNAME = '长江水库',WSYSTEMNAME='珠江流域',

STATIONCODE = '442000' ,STATIONNAME = '中山市环境监测站',STATEVALUE = 1,CODE_REGION='442000000000',REGIONNAME = '中山市'

WHERE POINTCODE = 'water_02'

 

 

INSERT INTO TENV.T_ENV_MONI_WATERPOINTINFO(PKID,STATIONCODE,STATIONNAME,POINTCODE,POINTNAME,

CODE_WQDATASOURCETYPE,WQDATASOURCETYPENAME,WSYSTEMNAME,CODE_REGION,REGIONNAME,STATEVALUE,CODE_WSYSTEMTYPE,MONITORTYPE,UPDATETIME)

SELECT SYS_GUID() PKID,'442000' STATIONCODE,'中山市环境监测站' STATIONNAME,'rhhl1006' POINTCODE,'洪奇沥' POINTNAME,'dbs' CODE_WQDATASOURCETYPE,

'入海河流' WQDATASOURCETYPENAME,'珠江流域' WSYSTEMNAME,'442000000000' CODE_REGION,'中山市' REGIONNAME,'1' STATEVALUE,

'rhhl' CODE_WSYSTEMTYPE,'1' MONITORTYPE ,SYSDATE UPDATETIME FROM DUAL

GO

 

城市河流加了一个白里口渡口

INSERT INTO TENV.T_ENV_MONI_WATERPOINTINFO(PKID,STATIONCODE,STATIONNAME,POINTCODE,POINTNAME,

WQDATASOURCETYPENAME,CODE_REGION,REGIONNAME,STATEVALUE,CODE_WSYSTEMTYPE,MONITORTYPE,UPDATETIME)

SELECT SYS_GUID() PKID,'442000' STATIONCODE,'中山市环境监测站' STATIONNAME,'cshl1022' POINTCODE,'白里口渡口' POINTNAME,

'城市河流' WQDATASOURCETYPENAME,'442000000000' CODE_REGION,'中山市' REGIONNAME,'1' STATEVALUE,

'cshl' CODE_WSYSTEMTYPE,'1' MONITORTYPE ,SYSDATE UPDATETIME FROM DUAL

GO

 

UPDATE TENV.T_ENV_MONI_WATERPOINTDATA_M A SET A.POINTCODE = (SELECT POINTCODE FROM TENV.T_ENV_MONI_WATERPOINTINFO B WHERE B.CODE_WSYSTEMTYPE = 'dbsyys' AND B.MONITORTYPE = 1 AND A.POINTNAME = B.POINTNAME AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM TENV.T_ENV_MONI_WATERPOINTINFO B WHERE B.CODE_WSYSTEMTYPE = 'dbsyys' AND B.MONITORTYPE = 1 AND ROWNUM = 1) AND A.POINTCODE IS NULL

 

 

UPDATE TENV.T_ENV_MONI_WATERPOINTDATA_M A SET A.POINTCODE = (SELECT POINTCODE FROM TENV.T_ENV_MONI_WATERPOINTINFO B WHERE B.CODE_WSYSTEMTYPE = 'cshl' AND B.MONITORTYPE = 1 AND A.POINTNAME = B.POINTNAME AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM TENV.T_ENV_MONI_WATERPOINTINFO B WHERE B.CODE_WSYSTEMTYPE = 'cshl' AND B.MONITORTYPE = 1 AND ROWNUM = 1) AND A.POINTCODE IS NULL

 

 

//SQLSERVER的减7问题

SELECT CONVERT(VARCHAR(100),DATEADD(DD,-7,MAX(REPORT_TIME)),23) + '~'+ CONVERT(VARCHAR(100),MAX(REPORT_TIME),23) SDATE FROM YWXFTS.DSJ_REPORTINFO WHERE REPORT_FROM='0'

 

//获取最大的监测时间

SELECT

CAST (MAX(CAST(A.监测年 AS VARCHAR)+'-'+CAST(A.监测月 AS VARCHAR)+'-'+CAST(A.监测日 AS VARCHAR)) AS DATE) FROM AUTOMONITOR.业务_废水监测数据 A

GO

 

//最大时间减去7的问题

SELECT CONVERT(VARCHAR(100),DATEADD(DAY,-7,CAST(MAX(CAST(A.监测年 AS VARCHAR)+'-'+CAST(A.监测月 AS VARCHAR)+'-'+CAST(A.监测日 AS VARCHAR)) AS DATE)),23)

+'~'+CONVERT(VARCHAR(100),CAST(MAX(CAST(A.监测年 AS VARCHAR)+'-'+CAST(A.监测月 AS VARCHAR)+'-'+CAST(A.监测日 AS VARCHAR)) AS DATE),23)

FROM AUTOMONITOR.业务_废水监测数据 A

 

//格式化后的时间

SELECT CAST(MAX(CAST(A.监测年 AS VARCHAR)+'-'+CAST(A.监测月 AS VARCHAR)+'-'+CAST(A.监测日 AS VARCHAR)) AS DATE) FROM AUTOMONITOR.业务_废水监测数据 A

 

//行政区

SELECT '' K ,'全部' V

UNION ALL

SELECT b.AREA_NAME k ,b.AREA_NAME v FROM ywpwxk.T_EmisTrade_EnterList a left join YWXFTS.DSJ_COD_AREAINFO B  on a.Code_RegionDetail = b.AREA_CODE+'000000' group by b.AREA_NAME

 

//新的行政区

SELECT '' A,'全部' B

UNION ALL

SELECT C.XZDQ A,C.XZDQ B

FROM HJTJ12.T_MID_NYYWR AS A

INNER JOIN HJTJ12.T_COD_QHXZQDM AS C

ON A.XZQDM=C.XZQDM AND (A.NF=C.NF OR (C.NF=2010 AND A.NF<2010))

WHERE A.HZFS='1' GROUP BY C.XZDQ

 

//行业类型

SELECT '' INDUSTRY_NAME,'全部' INDUSTRY_NAME

UNION ALL

SELECT C.INDUSTRY_NAME, C.INDUSTRY_NAME  FROM ywxfts.DSJ_COD_INDUSTRY C

//是否属实

select '' k,'全部' v

union all

select  c.k  k, c.k v from (

select

CASE WHEN A.WHETHER_CODE = '1' THEN '属实' WHEN A.WHETHER_CODE = '2' THEN '不属实' ELSE '部分属实' END k   from ywxfts.DSJ_COD_WHETHER a

) c