上班所用的SQL(一)

select SYS_DEPARTMENT_ID as dept_id,code,DEPARTMENT_NAME as dept_name from CREDIT_SYS_DEPARTMENT where ISDELETE ='0'
select * from (select tmp_tb.*,ROWNUM row_id from (select USERID,USERNAME,REALNAME,DEPSYSCODE,ISBRANCHADMIN,STATE,EMAIL,MOBILE,LASTLOGINTIME,LASTLOGINIP, NETSTATUS,DEPID,(select EC_NAME from zr_sys_code where ec_code='CA01' and ec_value =i.AREACODE) AREANAME, (select t.DEPARTMENT_NAME from CREDIT_SYS_DEPARTMENT t where t.CODE=i.depsyscode) depname from zr_sys_userinfo i WHERE 1=1 and DELETEMARK='0' order by ADDTIME DESC, USERNAME) tmp_tb ) where row_id>0 and row_id<= 10 \
select * from CREDIT_SYS_DEPARTMENT
select count(department_name) from CREDIT_SYS_DEPARTMENT
select table_name from dba_tab_columns where column_name='USERID'//不知道表名，根据已知字段查表。
select *from XYZX_FRSGSXZCF
SELECT *FROM XYZX_FRSGSXZXK

select code,department_name from credit_sys_department
select DEPSYSCODE,AJMC,CFJDRQ from xyzx_frsgsxzcf
select DEPSYSCODE,XMMC,XKJDRQ from xyzx_frsgsxzxk
select *from credit_sys_department,xyzx_frsgsxzcf,xyzx_frsgsxzxk where credit_sys_department.code=xyzx_frsgsxzcf.depsyscode and credit_sys_department.code=xyzx_frsgsxzxk.depsyscode
select distinct department_name from credit_sys_department//使不重复
select depsyscode,count(*) from xyzx_frsgsxzcf group by depsyscode//计算重复的有多少条
select depsyscode,count(*) from xyzx_frsgsxzxk group by depsyscode
select distinct department_name,code from credit_sys_department

SELECT
credit_sys_department.department_name,
credit_sys_department.code,
xyzx_frsgsxzcf.depsyscode,
xyzx_frsgsxzxk.depsyscode
FROM
credit_sys_department,
xyzx_frsgsxzcf,
xyzx_frsgsxzxk
WHERE
credit_sys_department.code = xyzx_frsgsxzcf.depsyscode and credit_sys_department.code=xyzx_frsgsxzxk.depsyscode


SELECT count(code),count(fcode),count(kcode) from (SELECT
credit_sys_department.department_name as name,
credit_sys_department.code as code,
xyzx_frsgsxzcf.depsyscode as fcode,
xyzx_frsgsxzxk.depsyscode as kcode
FROM
credit_sys_department,
xyzx_frsgsxzcf,
xyzx_frsgsxzxk
WHERE
credit_sys_department.code = xyzx_frsgsxzcf.depsyscode and credit_sys_department.code=xyzx_frsgsxzxk.depsyscode);

SELECT count(code),count(fcode) from (SELECT
credit_sys_department.department_name as name,
credit_sys_department.code as code,
xyzx_frsgsxzcf.depsyscode as fcode
FROM
credit_sys_department,
xyzx_frsgsxzcf
WHERE
credit_sys_department.code = xyzx_frsgsxzcf.depsyscode) group by code,fcode

//左连接 left join
SELECT *from credit_sys_department

left join xyzx_frsgsxzcf on credit_sys_department.code=xyzx_frsgsxzcf.depsyscode

select distinct department_name as name,code as codes,depsyscode as dcode from (SELECT *from credit_sys_department

left join xyzx_frsgsxzcf on credit_sys_department.code=xyzx_frsgsxzcf.depsyscode)

//第一步
select *from credit_sys_department c left join (select depsyscode,count(*) from xyzx_frsgsxzcf group by depsyscode) f on c.code=f.depsyscode

//第二步

select *from (select distinct department_name,code from credit_sys_department) c left join (select depsyscode,nvl(count(*),0) from xyzx_frsgsxzcf group by depsyscode) f on c.code=f.depsyscode

select *from (select distinct department_name,code from credit_sys_department) c left join (select depsyscode,count(*) from xyzx_frsgsxzxk group by depsyscode) k on c.code=k.depsyscode

//第三步
select *from
(select *from (select distinct department_name,code from credit_sys_department) c left join (select depsyscode,nvl(count(*),0) as fsum from xyzx_frsgsxzcf group by depsyscode) f on c.code=f.depsyscode) a,
(select *from (select distinct department_name,code from credit_sys_department) c left join (select depsyscode,nvl(count(*),0) as ksum from xyzx_frsgsxzxk group by depsyscode) k on c.code=k.depsyscode) b
where a.code=b.code

//优化
select fname,fsum,ksum,(fsum+ksum) as sum from
(select *from (select distinct department_name as fname,code from credit_sys_department) c left join (select depsyscode,nvl(count(*),0) as fsum from xyzx_frsgsxzcf group by depsyscode) f on c.code=f.depsyscode) a,
(select *from (select distinct department_name as kname,code from credit_sys_department) c left join (select depsyscode,nvl(count(*),0) as ksum from xyzx_frsgsxzxk group by depsyscode) k on c.code=k.depsyscode) b
where a.code=b.code