Oracle SQLサブクエリ
13853 ワード
>#サブクエリWHEREセクション提供(ネストされたサブクエリ)
関連しないサブクエリ(結合条件がない場合)、関連するサブクエリ(結合によって)によって区切られる1)関連しないサブクエリ
SELECT EMPLOYEE_ID,
EMP_NAME,
SALARY
FROM HR.EMP
WHERE SALARY >=(SELECT AVG(SALARY) --중첩 서브쿼리
FROM HR.EMP); SELECT A.EMPLOYEE_ID,
A.EMP_NAME,
A.SALARY
FROM HR.EMP A, (SELECT AVG(SALARY) AS A1
FROM HR.EMP)B --인라인 서브쿼리의 결과값이 B라는 뷰로 만들어짐.
WHERE A.SALARY>=B.A1; -- NON eqit 조인
SELECT A.EMPLOYEE_ID,
A.EMP_NAME,
A.SALARY
FROM HR.EMP A
WHERE EXISTS (SELECT
FROM (SELECT AVG(SALARY) AS A1
FROM HR.EMP)B
WHERE A.SALARY>=B.A1); --연관성이 있는 서브쿼리. A,B가 조인
Aliasは会員番号、会員名、購入金額、ポイントを提供しております
(메인쿼리: 회원정보 출력, 조건: 구매금액이 평균구매금액보다 많음)
-- 2005년 5월 회원별 구매 금액 합계
SELECT A.MEM_ID AS AID,
A.MEM_NAME AS ANAME,
SUM(B.CART_QTY*C.PROD_PRICE) AS ASUM
FROM MEMBER A, CART B, PROD C --구매금액 구하려고 B,C 테이블 필요
WHERE A.MEM_ID=B.CART_MEMBER --조인1
AND B.CART_PROD=C.PROD_ID --조인2
AND B.CART_NO LIKE '200506%'
GROUP BY A.MEM_ID, A.MEM_NAME ;
(서브쿼리: 2005년 6월 평균구매금액) --회원별 집계를 내고 평균값을 구해야함
SELECT AVG(C.BSUM)AS A1 --AVG와 SUM은 중첩될수 없음
FROM (SELECT A.CART_MEMBER, --연관성 없는 서브쿼리
SUM(A.CART_QTY*B.PROD_PRICE) AS BSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '200506%'
GROUP BY A.CART_MEMBER) C;
(결합)
SELECT A.MEM_ID AS AID,
A.MEM_NAME AS ANAME,
SUM(B.CART_QTY*C.PROD_PRICE) AS ASUM
FROM MEMBER A, CART B, PROD C --구매금액 구하려고 B,C 테이블 필요
WHERE A.MEM_ID=B.CART_MEMBER --조인1
AND B.CART_PROD=C.PROD_ID --조인2
AND B.CART_NO LIKE '200506%'
GROUP BY A.MEM_ID, A.MEM_NAME
-- HAVING SUM(B.CART_QTY*C.PROD_PRICE) >=(서브쿼리)
HAVING SUM(B.CART_QTY*C.PROD_PRICE) >= (SELECT AVG(C.BSUM)AS A1
FROM (SELECT A.CART_MEMBER,
SUM(A.CART_QTY*B.PROD_PRICE) AS BSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '200506%'
GROUP BY A.CART_MEMBER)C);サブクエリの関連付け
(메인쿼리: 회원의 회원번호, 회원명, 장바구니번호, 구매수량을 조회) -- 최종적으로 출력되는 부분
SELECT A.CART_MEMBER AS 회원번호,
B.MEM_NAME AS 회원명,
A.CART_NO AS 장바구니번호,
A.CART_QTY AS 구매수량
FROM CART A, MEMBER B
WHERE A.CART_MEMBER=B.MEM_ID
AND A.CART_QTY=(서브쿼리); -- 서브쿼리에서 계산된 카트 수량과 같은 값
(서브쿼리: 2005년 최고 구매수량)
SELECT MAX(C.CART_QTY)
FROM CART C
WHERE C.CART_NO LIKE '2005%'
AND C.CART_MEMBER=A.CART_MEMBER;
(결합)
SELECT A.CART_MEMBER AS 회원번호,
B.MEM_NAME AS 회원명,
A.CART_NO AS 장바구니번호,
A.CART_QTY AS 구매수량
FROM CART A, MEMBER B
WHERE A.CART_MEMBER=B.MEM_ID
AND A.CART_QTY=(SELECT MAX(C.CART_QTY)
FROM CART C
WHERE C.CART_NO LIKE '2005%'
AND C.CART_MEMBER=A.CART_MEMBER);
--SELECT文の結果はビューとカーソルです.SQLの影響を受ける行の集合
(2005年5月に購入金額が最も多かった5人)
SELECT A.AID
FROM (SELECT CART_MEMBER AS AID, --인라인 서브쿼리 5월에 구매한 사람 20명
SUM(CART_QTY*PROD_PRICE) AS ASUM
FROM CART, PROD
WHERE CART_NO LIKE '200505%'
AND CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5; --20명중 5명
DECLARE
CURSOR CUR_CART01 IS
SELECT A.AID, A.AAID
FROM (SELECT CART_MEMBER AS AID,
SUM(CART_QTY*PROD_PRICE) AS ASUM
FROM CART, PROD
WHERE CART_NO LIKE '200505%'
AND CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5;
V_NAME MEMBER.MEM_ID%TYPE;
V_SUMQ NUMBER:=0;
V_SUMC NUMBER:=0;
V_MID
BEGIN
FOR REC IN CUR_CART01 LOOP
V_MID:=REC.AAID
SELECT SUM(CART_QTY*PROD_PRICE),
SUM(CART_QTY) INTO V_SUMC, V_SUMQ
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_MEMBER=V_MID
AND SUSTR(CART_NO,1,6) BETWEEN '200504' AND '200507';
SELECT MEM_NAME INTO V_NAME
FROM MEMBER
DBMS_OUTPUT.PUT.LINE(
END LOOP;
END
**함수로 작성
CREATE OR REPLACE FUNCTOIN SUMC(
P_MID IN MEMBER.MEM_ID%TYPE,
P_MON IN VARCHAR2)
RETURN NUMBER
IS
V_SUM NUMBER:=0;
V_PERIOD CAHR(7):='2005'||P_MON||'%';
BEGIN
SELECT SUM(RPOD_PRICE*CART_QTY) INTO V_SUM
FROM CART, PROD
WHERE CART_MEMBER=P_MID
AND CART_NO LIKE V_PERIOD;
RETURN V_SUM;
END;
CREATE OR REPLACE FUNCTOIN SUMQ(
P_MID IN MEMBER.MEM_ID%TYPE,
P_MON IN VARCHAR2)
RETURN NUMBER
IS
V_SUM NUMBER:=0;
V_PERIOD CHAR(7):='2005'||P_MON||'%';
BEGIN
SELECT SUM(CART_QTY) INTO V_SUM
FROM CART, PROD
WHERE CART_NO LIKE V_PERIOD;
RETURN V_SUM;
END;
**실행
SELECT
FROM CART, PROD
WHERE CART_PROD=PROD_ID
Aliasは顧客コード顧客名購入数量合計購入金額合計を表します
(내부조인)
-- BUYER테이블과 BUYPROD 테이블을 연결하는 PROD테이블
SELECT B.BUYER_ID AS 거래처코드,
NVL(SUM(A.BUY_QTY),0) AS 매입수량합계,
NVL(SUM(A.BUY_QTY*C.PROD_PRICE),0) AS 매입금액합계
FROM BUYER B,BUYPROD A,PROD C
WHERE A.BUY_PROD=C.PROD_ID
AND C.PROD_BUYER=B.BUYER_ID
AND EXTRACT(YEAR FROM A.BUY_DATE) = 2005
GROUP BY B.BUYER_ID ;
(외부조인)
SELECT D.BUYER_ID AS 거래처코드,
D.BUYER_NAME AS 거래처명,
NVL(E.SUMQ,0) AS 매입수량합계,
NVL(E.SUMC,0) AS 매입금액합계
FROM BUYER D, (SELECT B.BUYER_ID AS BID, --서브쿼리는 참조되어지기 때문에 별칭을 영어로 쓰는게 좋음
SUM(A.BUY_QTY) AS SUMQ,
SUM(A.BUY_QTY*C.PROD_PRICE) AS SUMC
FROM BUYER B,BUYPROD A,PROD C
WHERE A.BUY_PROD=C.PROD_ID
AND C.PROD_BUYER=B.BUYER_ID
AND EXTRACT(YEAR FROM A.BUY_DATE) = 2005
GROUP BY B.BUYER_ID)E
WHERE E.BID(+)=D.BUYER_ID
ORDER BY 1;
Aliasは部門コード、部門名、国/地域、住所、人員、平均賃金を提供します.
SELECT CC.DEPARTMENT_ID AS 부서코드,
CC.DEPARTMENT_NAME AS 부서명,
DD.COUNTRY_NAME AS 국가,
BB.STREET_ADDRESS||''||BB.CITY||', '||BB.STATE_PROVINCE AS 주소,
AA.CNT AS 인원수,
AA.SAL AS 평균급여
FROM ( SELECT A.DEPARTMENT_ID AS DID,
COUNT(*) AS CNT,
ROUND(AVG(B.SALARY)) AS SAL
FROM HR.EMP B, HR.DEPT A
WHERE B.DEPARTMENT_ID=A.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID) AA,
HR.LOCATIONS BB, HR.DEPT CC, HR.COUNTRIES DD
WHERE AA.DID=CC.DEPARTMENT_ID
AND CC.LOCATION_ID=BB.LOCATION_ID
AND BB.COUNTRY_ID=DD.COUNTRY_ID
AND DD.COUNTRY_NAME!='United States of America';
-- 아래처럼 SELECT절에 COUNT, AVG함수올경우 GROUP BY절 해줘야하는데
부서코드, 부서명만 GROUP BY하면 상관없는데
-- 국가 주소도 있음. => COUNT, AVG구하는 쿼리를 뷰로 만들어버림
SELECT A.DEPARTMENT_ID AS 부서코드,
A.DEPARTMENT_NAME AS 부서명,
B.COUNTRY_NAME AS 국가,
C.STREET_ADDRESS||''||BB.CITY||', '||BB.STATE_PROVINCE AS 주소,
COUNT(*) AS 인원수,
ROUND(AVG(D.SALARY) AS 평균급여
FROM HR.DEPT A, HR.COUNTRIES B , HR.LOCATIONS C, HR.EMP D
サブクエリを使用したUPDATE文
(フォーマットを使用)
UPDATEテーブル名
SET[(カラム名[,カラム名,...])=(サブクエリ)
[WHERE条件];--更新するローの数
常用
サブクエリのSELECTセクションのカラムリストは、数、順序、およびタイプと一致する必要があります
使用例)従業員表で従業員の給与を所属部門の平均給与に更新してください.
(서브쿼리:부서별 평균급여)
SELECT DEPARTMENT_ID,
ROUND(AVG(SALARY)) AS ASAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID;
(메인쿼리: 사원의 급여 변경)
UPDATE HR.EMP A
SET A.SALARY=(SELECT B.ASAL
FROM (SELECT DEPARTMENT_ID, --부서별 평균급여 테이블
ROUND(AVG(SALARY)) AS ASAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID) --사원한명이 선택된 부서코드와 부서별 평균급여 테이블과 조인
7월 사용자 구매현황 마일리지
UPDATE MEMBER A
SET A.MEM_MILEAGE= (SELECT D.ASAL+A.MEM_MILEAGE
FROM (SELECT B.CART_MEMBER AS CID,
SUM(B.CART_QTY*C.PROD_MILEAGE) AS ASAL
FROM CART B, PROD C
WHERE B.CART_PROD=C.PROD_ID
AND B.CART_NO LIKE '200507%'
GROUP BY B.CART_MEMBER)D
WHERE A.MEM_ID=D.CID)
WHERE A.MEM_ID IN(SELECT DISTINCT CART_MEMBER --2005년 7월에 구매한 회원 번호를 중복없이 11명만 추출. WHERE절에 포함된것만 업데이트해라
FROM CART
WHERE CART_NO LIKE '200507%');
(검증)
SELECT MEM_ID, MEM_MILEAGE
FROM MEMBER;
COMMIT; UPDATE REMAIN C
SET C.REMAIN_O, C.REMAIN_J_99, C.REMAIN_DATE =
(SELECT D.AA+C.REMAIN_O, C.REMAIN_J_99-D.AA , TO_DATE('20050701')
FROM (SELECT A.CART_PROD AS ID,
SUM(A.CART_QTY) AS AA --판매수량
FROM CART A
WHERE SUBSTR(CART_NO,1,6) BETWEEN ('200505') AND('200507')
GROUP BY A.CART_PROD)D
WHERE C.PROD_ID D.ID)WHERE C.PROD_ID IN ( SELECT CART_PROD
FROM CART
WHERE SUBSTR(CART_NO,1,6) BETWEEN ('200505') AND('200507'));
UPDATE REMAIN A
SET A.REMAIN_I, A.REMAIN_J_99,A.REMAIN_DATE =
(SELECT A.REMAIN_I+B.CNT ,A.REMAIN_J_99++B.CNT, TO_DATE('20050701')
FROM (SELECT BUY_PROD AS BID,
SUM(BUY_QTY) AS CNT --매입개수
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050731')
GROUP BY BUY_PROD)B
WHERE A.PROD_ID = B.BID)
WHERE A.PROD_ID IN ( SELECT BUY_PROD
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050731'));
サブクエリのINSERTの使用
(フォーマットを使用)
INSERT INTO表名[(列名[,列名,...])]
サブクエリ
あまり使いません.データの初期化
INSERTで使用されるサブクエリは「()」に記述されていません
サブクエリのINSERTを使用してVALuES予約項目を省略
**テーブルのコピー
CREATE TABLE表名AS
サブクエリ
CREATE TABLE REMAIN_TEMP AS
SELECT * FROM REMAIN;
(검증)
SELECT * FROM REMAIN TEMP; DELETE REMAIN_TEMP;INSERT INTO REMAIN_TEMP
SELECT * FROM REMAIN
WHERE REMAIN_J_99<0;Reference
この問題について(Oracle SQLサブクエリ), 我々は、より多くの情報をここで見つけました https://velog.io/@vgo_dongv/Oracle-SQL-서브쿼리テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol