工作中遇到的SQL三（更新中）-优快云博客

本文链接：https://blog.youkuaiyun.com/fuwencaho/article/details/30486575

--full join 用法

  SELECT B.CHORGNAME,A.BK_END_CR_BAL1,A.BK_END_CR_BAL2,A.BK_END_CR_BAL1+A.BK_END_CR_BAL2 
  FROM 
(SELECT CASE WHEN A.BK_OPUN_COD IS NOT NULL THEN A.BK_OPUN_COD ELSE B.BK_OPUN_COD END BK_OPUN_COD,
COALESCE(A.BK_END_CR_BAL1,0)BK_END_CR_BAL1,COALESCE(B.BK_END_CR_BAL2,0)BK_END_CR_BAL2
FROM 
(SELECT BK_OPUN_COD,sum(BK_END_CR_BAL)BK_END_CR_BAL1 FROM RPT_BKCVTDRT 
 WHERE RPT_HEAD_DATE='20131231' AND BK_FIRST_LEVEL_LG_COD='2017' AND BK_SECOND_LEVEL_LG_CD='00'
AND BK_THIRD_LEVEL_LG_CD='00' AND CORPORATION='803' AND BK_BAL_DIRE='1' AND BK_LG_CHAR='1' GROUP BY BK_OPUN_COD)A

FULL JOIN  
 
(SELECT BK_OPUN_COD,sum(BK_END_CR_BAL)BK_END_CR_BAL2 FROM rpt_bkcvtdrt 
 WHERE RPT_HEAD_DATE='20131231' AND BK_FIRST_LEVEL_LG_COD='2018' AND BK_SECOND_LEVEL_LG_CD='00'
AND BK_THIRD_LEVEL_LG_CD='00' AND CORPORATION='803' AND BK_BAL_DIRE='1' AND BK_LG_CHAR='1' GROUP BY BK_OPUN_COD)B
 
ON a.BK_OPUN_COD=b.BK_OPUN_COD
)A 

INNER JOIN 

UPR_ORGANIZATION B ON A.BK_OPUN_COD=B.CHORGCODE ORDER BY A.BK_OPUN_CO

full join 解释

SELECT * FROM ORG;
SELECT * FROM staff

运行后：

DEPTNUMB	DEPTNAME	MANAGER	DIVISION	LOCATION
11	pianzif	160	Corporate	New York
15	New England	50	Eastern	Boston
20	Mid Atlantic	10	Eastern	Washington
38	South Atlantic	30	Eastern	Atlanta
42	Great Lakes	100	Midwest	Chicago
51	Plains	140	Midwest	Dallas
66	Pacific	270	Western	San Francisco
84	Mountain	290	Western	Denver
88	Head Office	160	Corporate	New York
121	Head Office	160	Corporate	New York
111	Head Office	160	Corporate	New York

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
10	Sanders	20	Mgr	7	18357.5
20	Pernal	20	Sales	8	18171.25	612.45
30	Marenghi	38	Mgr	5	17506.75
40	O'Brien	38	Sales	6	18006	846.55
50	Hanes	15	Mgr	10	20659.8
60	Quigley	38	Sales		16808.3	650.25
70	Rothman	15	Sales	7	16502.83	1152
80	James	20	Clerk		13504.6	128.2
90	Koonitz	42	Sales	6	18001.75	1386.7
100	Plotz	42	Mgr	7	18352.8
110	Ngan	15	Clerk	5	12508.2	206.6
120	Naughton	38	Clerk		12954.75	180
130	Yamaguchi	42	Clerk	6	10505.9	75.6
140	Fraye	51	Mgr	6	21150
150	Williams	51	Sales	6	19456.5	637.65
160	Molinare	10	Mgr	7	22959.2
170	Kermisch	15	Clerk	4	12258.5	110.1
180	Abrahams	38	Clerk	3	12009.75	236.5
190	Sneider	20	Clerk	8	14252.75	126.5
200	Scoutten	42	Clerk		11508.6	84.2
210	Lu	10	Mgr	10	20010
220	Smith	51	Sales	7	17654.5	992.8
230	Lundquist	51	Clerk	3	13369.8	189.65
240	Daniels	10	Mgr	5	19260.25
250	Wheeler	51	Clerk	6	14460	513.3
260	Jones	10	Mgr	12	21234
270	Lea	66	Mgr	9	18555.5
280	Wilson	66	Sales	9	18674.5	811.5
290	Quill	84	Mgr	10	19818
300	Davis	84	Sales	5	15454.5	806.1
310	Graham	66	Sales	13	21000	200.3
320	Gonzales	66	Sales	4	16858.2	844
330	Burke	66	Clerk	1	10988	55.5
340	Edwards	84	Sales	7	17844	1285
350	Gafney	84	Clerk	5	13030.5	188

------

SELECT * FROM STAFF FULL JOIN ORG ON DEPTNUMB=DEPT

10	Sanders	20	Mgr	7	18357.5		20	Mid Atlantic	10	Eastern	Washington
20	Pernal	20	Sales	8	18171.25	612.45	20	Mid Atlantic	10	Eastern	Washington
30	Marenghi	38	Mgr	5	17506.75		38	South Atlantic	30	Eastern	Atlanta
40	O'Brien	38	Sales	6	18006	846.55	38	South Atlantic	30	Eastern	Atlanta
50	Hanes	15	Mgr	10	20659.8		15	New England	50	Eastern	Boston
60	Quigley	38	Sales		16808.3	650.25	38	South Atlantic	30	Eastern	Atlanta
70	Rothman	15	Sales	7	16502.83	1152	15	New England	50	Eastern	Boston
80	James	20	Clerk		13504.6	128.2	20	Mid Atlantic	10	Eastern	Washington
90	Koonitz	42	Sales	6	18001.75	1386.7	42	Great Lakes	100	Midwest	Chicago
100	Plotz	42	Mgr	7	18352.8		42	Great Lakes	100	Midwest	Chicago
110	Ngan	15	Clerk	5	12508.2	206.6	15	New England	50	Eastern	Boston
120	Naughton	38	Clerk		12954.75	180	38	South Atlantic	30	Eastern	Atlanta
130	Yamaguchi	42	Clerk	6	10505.9	75.6	42	Great Lakes	100	Midwest	Chicago
140	Fraye	51	Mgr	6	21150		51	Plains	140	Midwest	Dallas
150	Williams	51	Sales	6	19456.5	637.65	51	Plains	140	Midwest	Dallas
170	Kermisch	15	Clerk	4	12258.5	110.1	15	New England	50	Eastern	Boston
180	Abrahams	38	Clerk	3	12009.75	236.5	38	South Atlantic	30	Eastern	Atlanta
190	Sneider	20	Clerk	8	14252.75	126.5	20	Mid Atlantic	10	Eastern	Washington
200	Scoutten	42	Clerk		11508.6	84.2	42	Great Lakes	100	Midwest	Chicago
220	Smith	51	Sales	7	17654.5	992.8	51	Plains	140	Midwest	Dallas
230	Lundquist	51	Clerk	3	13369.8	189.65	51	Plains	140	Midwest	Dallas
250	Wheeler	51	Clerk	6	14460	513.3	51	Plains	140	Midwest	Dallas
270	Lea	66	Mgr	9	18555.5		66	Pacific	270	Western	San Francisco
280	Wilson	66	Sales	9	18674.5	811.5	66	Pacific	270	Western	San Francisco
290	Quill	84	Mgr	10	19818		84	Mountain	290	Western	Denver
300	Davis	84	Sales	5	15454.5	806.1	84	Mountain	290	Western	Denver
310	Graham	66	Sales	13	21000	200.3	66	Pacific	270	Western	San Francisco
320	Gonzales	66	Sales	4	16858.2	844	66	Pacific	270	Western	San Francisco
330	Burke	66	Clerk	1	10988	55.5	66	Pacific	270	Western	San Francisco
340	Edwards	84	Sales	7	17844	1285	84	Mountain	290	Western	Denver
350	Gafney	84	Clerk	5	13030.5	188	84	Mountain	290	Western	Denver
							88	Head Office	160	Corporate	New York
							11	pianzif	160	Corporate	New York
							111	Head Office	160	Corporate	New York
							121	Head Office	160	Corporate	New York
160	Molinare	10	Mgr	7	22959.2
210	Lu	10	Mgr	10	20010
240	Daniels	10	Mgr	5	19260.25
260	Jones	10	Mgr	12	21234

----

--角色级别和机构级别不对应

SELECT a.username,a.ROLEID,a.STAID, b.BRLEVELS,c.BRLEVEL,c.BRID ,c.BRNAME FROM IFS_USER a INNER
JOIN IFS_ROLE b ON a.ROLEID=b.ROLEID INNER JOIN IFS_BRANCH c ON a.BRid=c.BRID WHERE b.BRLEVELS <>c.BRLEVEL

--substr函数的用法

SELECT * FROM IFS_STATION WHERE length(brlevels)>3

01,03
01,03,07
01,03,07

SELECT posstr(brlevels,',') FROM IFS_STATION WHERE length(brlevels)>3

3
3
3

SELECT substr(brlevels,1,posstr(brlevels,',')-1) FROM IFS_STATION WHERE length(brlevels)>3

01
01
01

-----------------

列变行，逗号分割

 SELECT * FROM IFSI_DISCUST_UPDATE WHERE custid='CN803100101621943'

CN803100101621943 UR80303053
CN803100101621943 UR80303319

SELECT A.CUSTID,
substr(VARCHAR(replace(replace(xml2clob(xmlagg(xmlelement(NAME a,A.OWNERID||','))),'</A>',''),'<A>','')),1,8000)OWNERID 
FROM 
IFSI_DISCUST_UPDATE A INNER JOIN IFS_USER B ON A.OWNERID=B.USERID 
 GROUP BY A.CUSTID

CN803100101621943 UR80303053,UR80303319,

-------

一个update需连表

SELECT oppstatus FROM IFS_OPPORTUNITY a INNER JOIN IFS_MARKETACT b
ON a.REFMARKETACT=b.MARKETACTID WHERE b.MARKETACTSTATE='03' AND a.oppstatus IN ('01','03')

将查出来的oppstatus 改为05

UPDATE IFS_OPPtest a SET a.OPPSTATUS='05'
WHERE EXISTS(SELECT 1 FROM IFS_MARtesT b
where a.REFMARKETACT=b.MARKETACTID and b.MARKETACTSTATE='03' AND a.oppstatus IN ('01','03') )

----一个看着很别捏的sql，暂且记下吧

分组排序取组内第一条
SELECT DISTINCT CUSTOMERID, MANAGEORGID,a FROM (
SELECT CUSTOMERID,MANAGEORGID,rank() over(PARTITION BY CUSTOMERID order by PUTOUTDATE DESC) a
FROM IFSI_CONTRACT
WHERE businesstype LIKE '3%' AND PUTOUTDATE IS NOT NULL AND CUSTOMERID = '20060422000922'
GROUP BY CUSTOMERID,MANAGEORGID,PUTOUTDATE
) WHERE A=1

SELECT CUSTOMERID,COUNT(*) FROM (
SELECT customerid,manageorgid FROM
( SELECT customerid,manageorgid,count(*) FROM IFSI_CONTRACT WHERE businesstype LIKE '3%'
GROUP BY customerid,manageorgid HAVING count(*) >1 ) A ) B GROUP BY CUSTOMERID HAVING COUNT(*) >1

--

SELECT B.CUSTCODE,B.CUSTNAME,B.ENGNAME,B.BUSNTYPE,B.ECONCHAR,B.GOVNCERTNO,B.ENTPCODE,B.TAXNUM,B.LNCERTNO,
CASE WHEN B.CORCUSTCODE IS NOT NULL AND B.CORCUSTCODE<>'' THEN 'CN'||B.CORCUSTCODE ELSE B.CORCUSTCODE END LEGALCORID,
C.CUSTNAME LEGCUSTNAME,C.CERTTYPE,C.CERTCODE,B.REGCAP,
CASE WHEN B.ENTPSCAL='01' THEN '10' WHEN B.ENTPSCAL='02' THEN '05' WHEN B.ENTPSCAL IN('03','04') THEN '01' ELSE '1020' END CUSTSCALES
FROM IFSI_HXKHJBXX B LEFT JOIN IFS_CUSTINFO C ON B.CORCUSTCODE=C.CUSTCODE