oracle常用sql語句

oracle數(shù)據(jù)庫相關(guān)學(xué)習(xí)

標(biāo)簽（空格分隔）： 數(shù)據(jù)庫

[toc]

1. 查詢oracle版本號(hào)

select * from v$version

2. 數(shù)據(jù)庫備份

打開數(shù)據(jù)庫服務(wù)器，cmd打開執(zhí)行如下語句

exp CHECK2014/CHECK2014@192.168.1.199/CHECK owner=CHECK2014 file=D:\SGHY_WPZF\CHECK2014-201805082312.dmp log=D:\SGHY_WPZF\CHECK2014-201805082312.log compress=n

D:\SGHY_WPZF為要保存?zhèn)浞輸?shù)據(jù)文件的路徑
用戶為CHECK2014，數(shù)據(jù)庫所在服務(wù)器地址為CHECK2014@192.168.1.199

3. 常用sql語句函數(shù)

1. sql中in和exist語句的區(qū)別

in和exists
in 是把外表和內(nèi)表作hash 連接，而exists是對外表作loop循環(huán)，每次loop循環(huán)再對內(nèi)表進(jìn)行查詢。
如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用exists，子查詢表小的用in：
例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)效率高，用到了B表上cc列的索引。
相反的2：select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)效率低，用到了A表上cc列的索引。
not in 和not exists如果查詢語句使用了not in 那么內(nèi)外表都進(jìn)行全表掃描，沒有用到索引；而not exists 的子查詢依然能用到表上的索引。所以無論那個(gè)表大，用not exists都比not in要快。
in 與 =的區(qū)別
select name from student where name in ('zhang','wang','li','zhao');與
select name from student where name='zhang' or name='li' or
name='wang' or name='zhao'
的結(jié)果是相同的。

2. 數(shù)據(jù)庫導(dǎo)出導(dǎo)入備份

導(dǎo)出

exp CHECK2014/CHECK2014@192.168.1.199/CHECK owner=CHECK2014 file=文件夾路徑\CHECK2014-201805082312.dmp  log=文件夾路徑\CHECK2014-201805082312.log compress=n

導(dǎo)入

imp US_OneMap/US_OneMap@localhost/orcl full=y ignore=y file= --導(dǎo)入數(shù)據(jù)的文件路徑(數(shù)據(jù)文件在Data文件夾下)

5. oracle sqlplus登陸

1、win鍵+R鍵，輸入cmd，打開命令提示符。
2、輸入sqlplus /nolog
3、繼續(xù)輸入conn /as sysdba
4、輸入 alter user 用戶名 identified by 密碼;
5、修改成功后，會(huì)有上圖“用戶已更改”的提示，再次登錄時(shí)用自己設(shè)定的密碼即可

6. ORACLE 11g登陸時(shí)出現(xiàn): 協(xié)議適配器錯(cuò)誤的解決方法

右鍵點(diǎn)擊計(jì)算機(jī)---> 服務(wù)與應(yīng)用程序 ---> 服務(wù) ---> 將oracle0raDb11g_home1ClrAgent、 oracleOraDb11g_home1TNSListener和oracleServiceORCL這三項(xiàng)啟動(dòng)即可

7.ORA-12504：TNS：監(jiān)聽程序在CONNECT_DATA中未獲得SERVICE_NAME

檢查tnsnames.ora文件中TNS是否配置正確，如下所示，SERVICE_NAME 名字弄錯(cuò)了，結(jié)果報(bào)如上錯(cuò)誤：

GSP =
　　(DESCRIPTION =
　　　　(ADDRESS =(PROTOCOL = TCP)(HOST = 172.20.36.79)(PORT = 1521))
　　　　(CONNECT_DATA=
　　　　　　(SERVER = DEDICATED)
　　　　　　(SERVER_NAME = gsp)
　　　　)
　　)

修改為

GSP =
　　(DESCRIPTION =
　　　　(ADDRESS =(PROTOCOL = TCP)(HOST = 172.20.36.79)(PORT = 1521))
　　　　(CONNECT_DATA=
　　　　　　(SERVER = DEDICATED)
　　　　　　(SERVICE_NAME = gsp)
　　　　)
　　)

1.監(jiān)聽服務(wù)沒有起起來。windows平臺(tái)個(gè)一如下操作：開始---程序---管理工具---服務(wù)，打開服務(wù)面板,啟動(dòng)oraclehome92TNSlistener服務(wù)。
2.database instance沒有起起來。windows平臺(tái)如下操作：開始---程序---管理工具---服務(wù)，打開服務(wù)面板，啟動(dòng)oracleserviceXXXX,XXXX就是你的database SID.
3.注冊表問題。regedit，然后進(jìn)入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0將該環(huán)境變量ORACLE_SID設(shè)置為XXXX,XXXX就是你的database SID.或者右?guī)孜业碾娔X，屬性--高級--環(huán)境變量---系統(tǒng)變量--新建，變量名=oracle_sid,變量值=XXXX,XXXX就是你的database SID.或者進(jìn)入sqlplus前，在command line下輸set oracle_sid=XXXX,XXXX就是你的database SID.
4.需要更改path中oracle的順序就可以了。
C:\oracle\product\10.2.0\db_1\bin;C:\oracle\product\10.2.0\client_1\bin;

8.oracle環(huán)境變量配置

ORACLE_HOME=D:\app\Administrator\product\12.1.0\dbhome_1（oracle安裝目錄）
Path=D:\app\Administrator\product\12.1.0\dbhome_1\BIN
ORACLE_SID=orcl（數(shù)據(jù)庫實(shí)例名）
TNS_ADMIN=D:\app\Administrator\product\12.1.0\dbhome_1\NETWORK\ADMIN
NLS_LANG=AMERICAN_AMERICA.AL32UTF8（utf8字符串）

9.oracle用戶創(chuàng)建

create user check0512 identified by check0512;
    grant connect,resource,dba to check0512; --把connect，resource權(quán)限授權(quán)給新用戶
grant dba to check0512 把dba權(quán)限授予給check0512

指定表空間

create user check0512 identified by wbs123 account unlock
default tablespace hjb
temporary tablespace hjb_temp;

為已有用戶指定表空間

alter user check0512  default tablespace userspace;

10.刪除用戶

刪除用戶：drop user hjb_wbs cascade;

刪除表空間

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

10.數(shù)據(jù)庫表空間查看

select tablespace_name,file_name,bytes from dba_data_files

11.創(chuàng)建表空間

create tablespace hjb_data datafile 'hj_data.dbf' size 50m
autoextend on next 50M maxsize unlimited

12.建表

create table usptotest
(
pn varchar(10) not null,
ss varchar(12) ,
isd varchar(20) not null  ,
title varchar(150) not null ,
abst varchar(2000) not null ,
appno varchar(20) not null  ,
appdate varchar(20) not null ,
inventor varchar(200) not null ,
assignee_name varchar(50) not null ,
assignee_country varchar(20) not null ,
assignee_city varchar(20) not null ,
assignee_state varchar(10) not null ,
primary key (pn)
)

13.用戶授權(quán)

grant select on DBA_DATA_FILES to US_OneMap;
grant select on DBA_FREE_SPACE to US_OneMap;
grant select on DBA_TABLES to US_OneMap;
grant select on DBA_TABLESPACES to US_OneMap;
-- Grant/Revoke role privileges 
grant connect to US_OneMap with admin option;
grant dba to US_OneMap with admin option;
grant resource to US_OneMap with admin option;
-- Grant/Revoke system privileges 
grant create view to US_OneMap;
grant select any table to US_OneMap;
grant unlimited tablespace to US_OneMap with admin option;

14. DROP MATERIALIZED VIEW <materialize view name>;

15.exp無法導(dǎo)出空表

鏈接
執(zhí)行如下語句

  1. 對空表分配segment，避免空表無法用exp導(dǎo)出
    alter system set deferred_segment_creation=false;
  2. 修改已有的空表（防止已存在的空表導(dǎo)不出）
    select 'alter table ' || table_name || ' allocate extent;' from user_tables where num_rows=0;
    復(fù)制查詢的語句并執(zhí)行；

16.表空間自動(dòng)擴(kuò)展

alter database datafile 'E:\APP\QINGYUAN\PRODUCT\11.2.0\DBHOME_1\DATABASE\TS_DZZW.DBF' 
  autoextend on next 100m

16.查詢表空間使用大小

SELECT a.tablespace_name "表空間名",

total "表空間大小",

free "表空間剩余大小",

(total - free) "表空間使用大小",

total / (1024 * 1024 * 1024) "表空間大小(G)",

free / (1024 * 1024 * 1024) "表空間剩余大小(G)",

(total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)",

round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name ;

17.arcsde庫查詢面積

SELECT SDE.st_astext(SHAPE) wkt FROM table WHERE objectid = ?

18. 如何解決 ORA-28595: Extproc 代理: DLL 路徑無效

但是一直提示如下錯(cuò)誤：

ORA-28595: Extproc 代理: DLL 路徑無效
ORA-06512: 在 "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 70
ORA-06512: 在 "SDE.ST_GEOMETRY_OPERATORS", line 68

百度了一番也沒找到方法，后來還是通過 google 查找到了線索，通過調(diào)試一番終于解決，在此給大家說明一下解決的思路。
我本機(jī)的 oracle11g 安裝目錄為 E:\oracle11g。

1、打開 E:\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN 目錄下的 listener.ora 文件，
發(fā)現(xiàn)之前寫的是 ：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\oracle11g\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\oracle11g\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
把它修改為：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\oracle11g\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ANY")
)
)
2、打開 E:\oracle11g\product\11.2.0\dbhome_1\hs\admin 目錄下的 extproc.ora文件，
最后一行原來寫的 是 SET EXTPROC_DLLS= ， 把他修改為 SET EXTPROC_DLLS=ANY
3、重啟 OracleOraDb11g_home1TNSListener服務(wù)和 OracleServiceORCL服務(wù)。
4、重啟 PL/SQL ，再次運(yùn)行 select sde.st_astext(shape) from DLWG_SQ， 這次終于不再提示錯(cuò)誤了，正常查詢到結(jié)果

19.查看dll

select * from user_libraries

19.修改dmp文件默認(rèn)的表空間名稱

利用notepat++打開導(dǎo)出dmp文件，搜索到表空間名稱，批量修改表空間
如:TABLESPACE "TS_ONEMAP"改為TABLESPACE "TS_ONEMAP_new"

20.數(shù)據(jù)庫優(yōu)化配置

1. 數(shù)據(jù)庫設(shè)置備份。

看部署文檔里面寫著如何寫腳本的。

2. 設(shè)置賬戶永不過期。

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

3. 設(shè)置密碼不區(qū)分大小寫

    ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

4. 對空表分配segment，避免空表無法用exp導(dǎo)出

alter system set deferred_segment_creation=false;

5. 修改已有的空表（防止已存在的空表導(dǎo)不出）

select 'alter table ' || table_name || ' allocate extent;' from user_tables where num_rows=0;

將上述語句查出的結(jié)果，進(jìn)行運(yùn)行。

6. 擴(kuò)大open_cursors參數(shù)

alter system set open_cursors=3000 scope=both;
alter system set session_cached_cursors=100 scope=spfile;
alter system set cursor_space_for_time=true scope=spfile;

7. 擴(kuò)大實(shí)例數(shù)

alter system set processes = 300 scope = spfile;

8. 索引成本和緩存相關(guān)

alter system set optimizer_index_cost_adj=20;
alter system set optimizer_index_caching=80;

9. 兩數(shù)據(jù)庫之間表的比較

/*** 對比兩個(gè)用戶下的表名稱，檢查數(shù)據(jù)遷移中是否遺漏表
**使用方法如下：
*** 1. 用PL/SQL 登陸原數(shù)據(jù)庫
*** 2. 下面語句中【原數(shù)據(jù)庫用戶名】填寫當(dāng)前用戶名
*** 3. 創(chuàng)建一個(gè)遷移數(shù)據(jù)庫的dbLink
*** 4. 【dbLink名稱】的填寫創(chuàng)建的dbLink名稱
*** 5. 【遷移后的數(shù)據(jù)庫用戶名】填寫新遷移的數(shù)據(jù)庫用戶名
*** 6. 執(zhí)行語句查看結(jié)果，如果有記錄，說明新數(shù)據(jù)庫中缺這幾個(gè)表
**/

    select t.TABLE_NAME from all_tables t 
       where owner=upper('原數(shù)據(jù)庫用戶名')
         and t.TABLE_NAME not in(select p.TABLE_NAME from all_tables@dbLink名稱 p where owner=upper('遷移后的數(shù)據(jù)庫用戶名'));

21. orcle error 12899

https://blog.csdn.net/iamlaosong/article/details/52316844

22.parttion by

Parttion by關(guān)鍵字是Oracle中分析性函數(shù)的一部分，用于給結(jié)果集進(jìn)行分區(qū),它和聚合函數(shù)Group by不同的地方在于它只是將原始數(shù)據(jù)進(jìn)行名次排列，能夠返回一個(gè)分組中的多條記錄（記錄數(shù)不變），而Group by是對原始數(shù)據(jù)進(jìn)行聚合統(tǒng)計(jì)，一般只有一條反映統(tǒng)計(jì)值的結(jié)果（每組返回一條）。

23.數(shù)字操作

-- select MOD(65,50) from dual     --取余 
-- select  trunc( 65/33) from dual  -- 取整 trunc (1.9) = 1
-- select ceil(65/60) from dual          -- 取整 ceil(1.1) = 2

24. 視圖創(chuàng)建

create or replace view view_hz as
select
--listagg(m.jctbbh,',')  WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
 ,count(*)as sl,xzqdm,'兵團(tuán)圖斑' as fl,1 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '兵團(tuán)圖斑'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
     union all
           --規(guī)劃院 合法圖斑
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
 WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'合法圖斑' as fl,2 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '合法圖斑'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

             union all
--建設(shè)用地處 國家和自治區(qū)重點(diǎn)工程項(xiàng)目
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
 WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'國家和自治區(qū)重點(diǎn)工程項(xiàng)目' as fl,3 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '國家和自治區(qū)重點(diǎn)工程項(xiàng)目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

           union all
--建設(shè)用地處 民生工程
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'民生工程' as fl,4 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '民生工程項(xiàng)目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm



           union all
--建設(shè)用地處 援疆項(xiàng)目
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'援疆項(xiàng)目' as fl,5 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '援疆項(xiàng)目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
            union all
--建設(shè)用地處 自行糾正前
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'自行糾正前' as fl,6 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '自行糾正'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and ( t.sftg='0' or t.sftg='11') --自行糾正點(diǎn)擊通過、2018年前、2018年后
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'自行糾正后' as fl,18 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '自行糾正'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and (t.sftg='12') --自行糾正點(diǎn)擊通過、2018年前、2018年后
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--執(zhí)法處 移交其他部門
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'移交其他部門' as fl,7 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '移交其他部門'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
            union all
--執(zhí)法處 違法已結(jié)案
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'違法已結(jié)案' as fl,8 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where ( m.tbflqk='違法已立案' or m.tbflqk='違法已結(jié)案' )
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

           union all
--執(zhí)法處 違法未處理
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'違法未處理' as fl,9 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where (m.tbflqk ='違法未處理' )  --or m.tbflqk='移交其他部門'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.wtglx='6'
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--執(zhí)法處 違法未結(jié)案
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'違法未結(jié)案' as fl,10 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where ( m.tbflqk='違法未結(jié)案' ) --or m.tbflqk='移交其他部門'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.wtglx='5'
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

           union all
--耕保處 設(shè)施農(nóng)用地
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'設(shè)施農(nóng)用地' as fl,11 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where (m.tbflqk = '變更審核通過的設(shè)施農(nóng)用地' or m.tbflqk = '設(shè)施農(nóng)用地')
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '5'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--利用處 臨時(shí)用地
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'臨時(shí)用地' as fl,12 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '臨時(shí)用地'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '1'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
            union all
--地籍處 農(nóng)村道路用地
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'農(nóng)村道路用地' as fl,13 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '農(nóng)村道路用地'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '4'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--地籍處 實(shí)地偽變化
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'實(shí)地偽變化' as fl,14 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '實(shí)地偽變化'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '4'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--建設(shè)用地處 不改變原用地性質(zhì)的光伏項(xiàng)目
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'光伏項(xiàng)目用地' as fl,15 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '不改變原用地性質(zhì)的光伏項(xiàng)目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and (t.sftg=0 and t.sftg=2)
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--規(guī)劃院 軍事用地圖斑
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'軍事用地圖斑' as fl,16 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.qtydtbflqk = '特殊用地'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容