とても可読性の良いSQLを取引先様から教えていただいたのでメモ
SELECTのみに可能
With
A1 As (
SELECT
TOKCD
, BUSCD
FROM
ORCL.BUSYOPF
WHERE
1=1
)
, A2 As ( --複数部署を持つ、得意先
SELECT
TOKCD
,Count(BUSCD) Cnt
FROM
ORCL.BUSYOPF
GROUP BY
TOKCD
Having Count(BUSCD)>1
)
, A3 As ( --複数部署のみ抽出
SELECT
A1.TOKCD
, A1.BUSCD
, A2.Cnt
FROM A2
INNER JOIN A1 ON A2.TOKCD = A1.TOKCD
ORDER BY
A1.TOKCD
, A1.BUSCD
)
--複数の自分以外のリスト
SELECT
LIST.TOKCD
,LIST.BUSCD
,AITE.TOKCD
,AITE.BUSCD
FROM
A3 LIST
INNER JOIN
A3 AITE
ON LIST.TOKCD = AITE.TOKCD
AND LIST.BUSCD <> AITE.BUSCD
ORDER BY
LIST.TOKCD
,LIST.BUSCD
WITH
F AS (
SELECT SYOZCD
,SYOZRN
,TANCD
,TANNMJ
,TOKCD
,BUSCD
,TOKCD||LPAD(BUSCD,3,0) TOKBUS
,TOKRN
,BUSRN
,SIRCD
,SIRRN
,SYOCD
,CASE
WHEN SIRCD = 1402
AND INSTR(SYOCD , '-') <> 0 THEN SUBSTR(SYOCD , 1 , INSTR(SYOCD , '-') - 1)
ELSE SYOCD
END SYOCDN , ZAIKBN , ZAIKBNNM , BASCD1 , BASCD2 , JZAIKO , ZTANKA , ZKINGK , NYUKBI , SYUKBI , KIJYUN6 , HANTEI , HANTEINM , BIKO
FROM FUDOZAIKO )
,U AS (
SELECT URI.SYOZCD
,URI.TANCD
,TAN.TANNMJ
,URI.TOKCD
,URI.BUSCD
,TOK.TOKRN
,BUS.BUSRN
,URI.TOKBUS
,URI.SIRCD
,URI.SYOCD
,URI.SYOCDN
,MAX(URI.SYUKBI) SYUKBIMAX
,COUNT(URI.SYUKBI) SYUKBICNT
FROM
(
SELECT
SYOZCD
,TANCD
,TOKCD
,BUSCD
,TOKCD||LPAD(BUSCD,3,0) TOKBUS
,SIRCD
,SYOCD
,CASE
WHEN SIRCD = 1402
AND INSTR(SYOCD , '-') <> 0 THEN SUBSTR(SYOCD , 1 , INSTR(SYOCD , '-') - 1)
ELSE SYOCD
END SYOCDN
,SYUKBI
FROM URIKEPF
WHERE SYUKBI >= 20160201
) URI
LEFT JOIN TOKUIPF TOK ON URI.TOKCD = TOK.TOKCD
LEFT JOIN BUSYOPF BUS ON URI.BUSCD = BUS.BUSCD AND URI.TOKCD = BUS.TOKCD
LEFT JOIN TANTOPF TAN ON URI.TANCD = TAN.TANCD
GROUP BY
URI.SYOZCD
,URI.TANCD
,TAN.TANNMJ
,URI.TOKCD
,URI.BUSCD
,TOK.TOKRN
,BUS.BUSRN
,URI.TOKBUS
,URI.SIRCD
,URI.SYOCD
,URI.SYOCDN)
SELECT F.SYOZCD
,F.SYOZRN
,F.TANCD
,F.TANNMJ
,F.TOKCD
,F.BUSCD
,F.TOKBUS
,F.TOKRN
,F.BUSRN
,F.SIRCD
,F.SIRRN
,F.SYOCD
,F.SYOCDN
,F.ZAIKBN
,F.ZAIKBNNM
,F.BASCD1
,F.BASCD2
,F.JZAIKO
,F.ZTANKA
,F.ZKINGK
,F.NYUKBI
,F.SYUKBI
,F.KIJYUN6
,F.HANTEI
,F.HANTEINM
,F.BIKO
,U.TOKCD
,U.BUSCD
,U.TOKRN
,U.BUSRN
,U.TANNMJ
,U.SYUKBIMAX
,U.SYUKBICNT
FROM F
LEFT JOIN U ON F.SIRCD = U.SIRCD
AND F.SYOCDN = U.SYOCDN
AND F.TOKBUS <> U.TOKBUS
AND F.SYOZCD = U.SYOZCD
ORDER BY F.SYOZCD
,F.TANCD
,F.TOKCD
,F.BUSCD
,F.SIRCD
,F.SYOCDN
コメント