CROSS JOIN
- "모든 경우를 고려한다"를 의미
- 테이블 상호간에 연결될 수 있는 모든 경우의 수를 산출하여 나타내는 조인
- Cartesian Product값을 얻을 때 사용
예)
SELECT
T1.AMT_ODR
, T2.AMT_ODR
, (DSTB_AMT_SUM / NMPR_CNT) AS ID_RATE
FROM
(
SELECT 1 AS AMT_ODR, DSTB_AMT_SUM
FROM MA_RT_PCST
WHERE FACC_YYMM = '201809'
AND MNGM_ACNT_CD = 'T60000000'
AND MNGM_DEPT_CD = '144260' /* 영업이익 */
UNION ALL
SELECT 2 AS AMT_ODR, DSTB_AMT_SUM
FROM MA_RT_PCST
WHERE FACC_YYMM = '201809'
AND MNGM_ACNT_CD = 'T90000000'
AND MNGM_DEPT_CD = '144260' /* 세전이익 */
UNION ALL
SELECT 3 AS AMT_ODR, SUM(OORD_AMT)
FROM MA_RT_MM_CLSG_PRFT
WHERE FACC_YYMM = '201809'
AND MNGM_DEPT_CD = '144260' /* 수주실적 */
UNION ALL
SELECT 4 AS AMT_ODR, SUM(OORD_NEW_DPST + OORD_CAFW_DPST)
FROM MA_RT_MM_CLSG_PRFT
WHERE FACC_YYMM = '201809'
AND MNGM_DEPT_CD = '144260' /* 입금실적 */
) T1
CROSS JOIN
(
SELECT 1 AS AMT_ODR, SUM(SLRY_PAY_AMT) AS NMPR_CNT
FROM MA_MI_DEPT_SLRY
WHERE FACC_YYMM = '201809'
AND CMPN_CD = '1000'
AND DSTB_STDR = 'C0023'
AND DEPT_CD = '111136' /* 인건비 */
UNION ALL
SELECT 2 AS AMT_ODR, NMPR_CNT
FROM MA_MI_DEPT_NMPR
WHERE FACC_YYMM = '201809'
AND CMPN_CD = '1000'
AND DSTB_STDR ='C0020'
AND DEPT_CD = '144260' /* 인원수 */
) T2
ORDER BY T2.AMT_ODR, T1.AMT_ODR
| cs |