우선 어제의 질문 url은 <div><a target="_blank" href="http://www.todayhumor.co.kr/board/view.php?table=programmer&no=12593&s_no=12593&page=1">http://www.todayhumor.co.kr/board/view.php?table=programmer&no=12593&s_no=12593&page=1</a> 입니다.</div> <div><br></div> <div>저는 sql환경이 마리아sql입니다. 위 질문에 대해서 해결은 해서 정보를 올리고자 합니다.</div> <div>우선 어제 sql의 문제점은 inner join으로 member 컬럼을 넣을때 순서가 깨지는 현상 이였습니다.</div> <div><br></div> <div>그로인해 해결책은 서브쿼리에 inner join을 해서 순서가 깨진 서브쿼리정보를 order by 해서 그걸 다시</div> <div>select 시키자 ( 번호를 매기자 ) 였습니다. 그리고 그대로 서브쿼리에 inner join을 넣고 order by 를 해서 순서가 잘 나오는걸 확인했습니다.</div> <div>그뒤 메인 쿼리로 select 시켰을때 마찬가지로 순서가 깨지는 현상이 일어났습니다. 그래서 3시간끝에 포기를 햇었습니다.</div> <div>그리고 좀 쉰뒤에 다른 방법으로 했을때 성공 했습니다. 아래가 실패한 쿼리와 성공한 쿼리입니다.</div> <div><br></div> <div>/////////////////////////////////////////////////실패 쿼리 ////////////////////////////////////////////</div> <div><pre><code><span class="sql1-symbol"></span></code></pre><pre><code><code><span class="sql1-reservedword">set</span><span class="sql1-space"> </span><span class="sql1-symbol">@</span><span class="sql1-identifier">board_number2</span><span class="sql1-symbol">:=</span><span class="sql1-number">0</span><span class="sql1-symbol">; </span><span class="sql1-reservedword">set</span><span class="sql1-space"> </span><span class="sql1-symbol">@</span><span class="sql1-identifier">board_number</span><span class="sql1-symbol">:=</span><span class="sql1-number">10</span><span class="sql1-symbol">; </span><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-symbol">(@</span><span class="sql1-identifier">board_number</span><span class="sql1-symbol">:=@</span><span class="sql1-identifier">board_number</span><span class="sql1-symbol">-</span><span class="sql1-number">1</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-identifier">board_number</span><span class="sql1-space"> </span><span class="sql1-symbol">,(@</span><span class="sql1-identifier">board_number2</span><span class="sql1-symbol">:=@</span><span class="sql1-identifier">board_number2</span><span class="sql1-symbol">+</span><span class="sql1-number">1</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-identifier">board_number2</span><span class="sql1-space"> </span><span class="sql1-symbol">, </span><span class="sql1-identifier">TMP</span><span class="sql1-symbol">.* </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-symbol">( </span><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.*,</span><span class="sql1-tablename">memberm_M</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberAccount </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D </span><span class="sql1-reservedword">inner</span><span class="sql1-space"> </span><span class="sql1-reservedword">join</span><span class="sql1-space"> </span><span class="sql1-tablename">memberm_M</span><span class="sql1-space"> </span><span class="sql1-reservedword">on</span><span class="sql1-space"> </span><span class="sql1-tablename">memberm_M</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberPK</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberPK </span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-function">isnull</span><span class="sql1-symbol">(</span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">isDel</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">and</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">productPK</span><span class="sql1-symbol">=</span><span class="sql1-space"> </span><span class="sql1-string">'000000000004' </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterDate</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterPK</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc </span><span class="sql1-symbol">)</span><span class="sql1-identifier">TMP </span></code></code></pre><code></code></div> <div><br></div> <div><br></div> <div><br></div> <div>////////////////////////////////////////////////// 성공 쿼리 ////////////////////////////////////////</div> <div><pre><code><span class="sql1-reservedword">set</span><span class="sql1-space"> </span><span class="sql1-symbol">@</span><span class="sql1-identifier">board_number2</span><span class="sql1-symbol">:=</span><span class="sql1-number">0</span><span class="sql1-symbol">; </span><span class="sql1-reservedword">set</span><span class="sql1-space"> </span><span class="sql1-symbol">@</span><span class="sql1-identifier">board_number</span><span class="sql1-symbol">:=10;</span><span class="sql1-symbol"> </span><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-identifier">TMP2</span><span class="sql1-symbol">.*</span><span class="sql1-space"> </span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-tablename">memberm_M</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberAccount</span><span class="sql1-symbol">,</span><span class="sql1-tablename">memberm_M</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberPK </span><span class="sql1-reservedword">from </span><span class="sql1-symbol">( </span><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-symbol">(@</span><span class="sql1-identifier">board_number</span><span class="sql1-symbol">:=@</span><span class="sql1-identifier">board_number</span><span class="sql1-symbol">-</span><span class="sql1-number">1</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-identifier">board_number</span><span class="sql1-space"> </span><span class="sql1-symbol">,(@</span><span class="sql1-identifier">board_number2</span><span class="sql1-symbol">:=@</span><span class="sql1-identifier">board_number2</span><span class="sql1-symbol">+</span><span class="sql1-number">1</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-identifier">board_number2</span><span class="sql1-space"> </span><span class="sql1-symbol">, </span><span class="sql1-identifier">TMP</span><span class="sql1-symbol">.* </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-symbol">( </span><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.* </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D </span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-function">isnull</span><span class="sql1-symbol">(</span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">isDel</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">and</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">productPK</span><span class="sql1-symbol">=</span><span class="sql1-space"> </span><span class="sql1-identifier">Param1 </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterDate</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-tablename">afterReview_D</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterPK</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc </span><span class="sql1-symbol">)</span><span class="sql1-identifier">TMP </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-identifier">TMP</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterDate</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-identifier">TMP</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterPK</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc </span><span class="sql1-symbol">)</span><span class="sql1-identifier">TMP2 </span><span class="sql1-reservedword">inner</span><span class="sql1-space"> </span><span class="sql1-reservedword">join</span><span class="sql1-space"> </span><span class="sql1-tablename">memberm_M</span><span class="sql1-space"> </span><span class="sql1-reservedword">on</span><span class="sql1-space"> </span><span class="sql1-tablename">memberm_M</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberPK</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-space"> </span><span class="sql1-identifier">TMP2</span><span class="sql1-symbol">.</span><span class="sql1-identifier">memberPK </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-identifier">TMP2</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterDate</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-identifier">TMP2</span><span class="sql1-symbol">.</span><span class="sql1-identifier">afterPK</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">; </span></code></pre><pre><code><span class="sql1-symbol"><br></span></code></pre><pre><code><span class="sql1-symbol"><br></span></code></pre><pre>간단한 설명을 드리자면 실패 원인은 확실히 inner join을 해서 순서가 깨지는거였습니다. 그래서 번호를 매기는 순서를 inner join 전에 order by로 매기고 나서 inner join을 하면 된다. 즉 inner join 후 에러이면 inner join 전에 번호 매기고 나서 inner join을 하면 된다 였습니다. <br></pre><pre>진짜 어떻게 해야 하나 걱정을 많이 했었는데 편법으로 해결은 했습니다. 많은 조언 주셔서 감사합니다</pre></div>
댓글 분란 또는 분쟁 때문에 전체 댓글이 블라인드 처리되었습니다.