현재 위치

홈 ›› Forums ›› 공부 ›› 프로그래밍 QnA

오라클 SQLPLUS 추출해내는 코드.

georgek의 이미지
글쓴이: georgek / 작성시간: 금, 2013/11/29 - 2:40오후

안녕하세요. 저번에 트리거관련 질문을 올렸었던 사람입니다.

요즘 원서를 보면서 공부하고있는데, 연습문제중에 다음과 같은 문제가 있는데 코드를 짜고나서 추출하면 답이 답지와 다르게 나오는데
제 코드가 어디서 잘못됐는지 못찾겠어서 여기계신분들께 여쭙고자 글을 올리게 되었습니다.

문제는 다음과 같습니다.
the program will ask for two dates, in “YYYY- MM-DD”format. After you input the two dates(from 1998- 02-01 to 1998- 05-01), the program displays the client IDs and company names of those which didn’t make any order between the given two dates.

1998-02-01 ~ 1998-05-01 기간동안 주문을 안한 clientid와 회사이름을 추출해 내는것이라서 다음과 같이 코드를 짜봤는데, 아무리 생각해봐도 어디서 잘못됐는지를 찾지 못하겠습니다.(답답하네요ㅜㅜ) _ 테이블이나 이런 데이터들은 일단 다 만들어 놓고 시작하는것이라 책과 차이는 없을것같은데 답지가 잘못된것인지..
제가 짜본 코드는 다음과 같습니다.

select clientid as "Client ID" ,companyname as "Company Name" from clients where clientid in ((select clientid from orders) minus (select clientid from orders where orderdate between to_date('1998-02-01','yyyy-mm-dd') and to_date('1998-05-01','yyyy-mm-dd')))

뭔가 잘못되었다면 지적해주시면 감사하겠습니다 .

감사합니다!!

Forums: 
프로그래밍 QnA
익명 사용자의 이미지

글쓴이: 익명 사용자 / 작성시간: 금, 2013/11/29 - 3:11오후

기간에 상관없이 한 건도 주문하지 않은 회사는 나오지 않겠네요.

georgek의 이미지

글쓴이: georgek / 작성시간: 금, 2013/11/29 - 5:57오후

아래 chanik님께서 알려주셨네요. 감사합니다.

chanik의 이미지

글쓴이: chanik / 작성시간: 금, 2013/11/29 - 3:28오후

위에 익명님이 지적하신대로입니다. 아래와 같이 하시면 될 것 같네요.

SELECT clientid AS "Client ID", companyname AS "Company Name" FROM clients
 WHERE clientid NOT IN
  (SELECT clientid FROM orders WHERE orderdate 
     BETWEEN TO_DATE('1998-02-01','yyyy-mm-dd') AND TO_DATE('1998-05-01','yyyy-mm-dd'));
georgek의 이미지

글쓴이: georgek / 작성시간: 금, 2013/11/29 - 5:58오후

저번에도 그렇고 이번에도 감사합니다.
여기서 제가 쓴건.

~~~ in select ~~ minus select ~~ between ~~~.인데

chanik님께서 알려주신것과 어디서 차이가 나는건지 알 수 있을까요?
저는 이게 똑같은거라 생각해왔었습니다..

아무튼 너무 감사합니다.!!

chanik의 이미지

글쓴이: chanik / 작성시간: 금, 2013/11/29 - 11:47오후

우선, 작성하신 query는 아래와 같이 정리하여 내부의 select문을 하나 제거해도 동일한 결과가 나올 것입니다.

SELECT clientid AS "Client ID", companyname AS "Company Name" FROM clients
 WHERE clientid IN 
  (SELECT clientid FROM orders WHERE orderdate
     NOT BETWEEN TO_DATE('1998-02-01','yyyy-mm-dd') AND TO_DATE('1998-05-01','yyyy-mm-dd'));

아래과 같이 가정해보겠습니다.

- 전체 고객의 수 : 100명 (clients 테이블에 등록된 고객 수)
- 한 번이라도 주문한 적이 있는 고객의 수 : 80명 (orders 테이블에 등장하는 고객 수)
- 위의 기간동안 주문한 적이 있는 고객의 수 : 60명

문제가 요구한 것은 "위 기간동안 주문한 적이 없는 고객의 목록"이므로 정답은 100명 - 60명 = 40명의 목록이 될 것입니다.
하지만 작성하신 쿼리는 orders 테이블, 즉 한 번이라도 주문한 적이 있는 고객만을 대상으로 clientid를 골라내고 있으므로 80명 - 60명 = 20명의 명단만을 출력하게 됩니다. 즉, 한 번도 주문한 적이 없는 고객 20명 때문에 차이가 나는 셈이고, 이것이 바로 위의 익명님이 지적하신 점입니다.

만약 모든 고객들이 한 번씩이라도 주문을 한 적이 있다면 clients 테이블과 orders 테이블 모두에 100명의 고객들이 다 등장할테니, 작성하신 쿼리로도 정답이 나올 것입니다.

chanik의 이미지

글쓴이: chanik / 작성시간: 토, 2013/11/30 - 12:46오전

위 문제는 join을 통해서도 풀 수 있겠는데, 이 경우에도 비슷한 비교를 해 볼 수 있습니다.

아래와 같이 inner join으로 작성하면 20명의 목록만을 보여주게 될 것입니다.

SELECT DISTINCT c.clientid AS "Client ID", c.companyname AS "Company Name"
  FROM clients c JOIN orders o ON c.clientid = o.clientid
 WHERE o.orderdate NOT BETWEEN to_date('1998-02-01','yyyy-mm-dd') AND to_date('1998-05-01','yyyy-mm-dd');

하지만 아래와 같이 left outer join을 하게 되면 clients 테이블의 레코드 가운데 orders 테이블에 등장하지 않는 것들도 빠뜨리지 않게 되므로 40명의 목록을 다 보여주게 됩니다.

SELECT DISTINCT c.clientid AS "Client ID", c.companyname AS "Company Name"
  FROM clients c LEFT OUTER JOIN orders o ON c.clientid = o.clientid
 WHERE o.orderdate NOT BETWEEN to_date('1998-02-01','yyyy-mm-dd') AND to_date('1998-05-01','yyyy-mm-dd')
    OR o.orderdate IS NULL;



위 문법은 ANSI SQL 문법이고, 각각 아래와 같이 오라클 전용 문법으로 표시할 수도 있습니다. 

SELECT DISTINCT c.clientid AS "Client ID", c.companyname AS "Company Name"
  FROM clients c, orders o
 WHERE c.clientid = o.clientid
   AND o.orderdate NOT BETWEEN to_date('1998-02-01','yyyy-mm-dd') AND to_date('1998-05-01','yyyy-mm-dd');

SELECT DISTINCT c.clientid AS "Client ID", c.companyname AS "Company Name"
  FROM clients c, orders o
 WHERE c.clientid = o.clientid(+)
   AND (o.orderdate NOT BETWEEN to_date('1998-02-01','yyyy-mm-dd') AND to_date('1998-05-01','yyyy-mm-dd')
        OR o.orderdate IS NULL);

오라클을 쓸 수 있는 상황이 아니어서 확인하지 못했으므로, sql문에 오류가 있을지 모릅니다.

참고 : http://www.orafaq.com/wiki/Outer_join

georgek의 이미지

글쓴이: georgek / 작성시간: 화, 2013/12/03 - 12:08오후

평소에 잘 모르고 그냥 쓰고 있었는데,, 정말 감사합니다!!

댓글 달기

텍스트 포맷에 대한 자세한 정보

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.