FireBird Forum
C++Builder  |  Delphi  |  FireMonkey  |  C/C++  |  Free Pascal  |  Firebird
볼랜드포럼 BorlandForum
 경고! 게시물 작성자의 사전 허락없는 메일주소 추출행위 절대 금지
파이어버드 포럼
Q & A
FAQ
팁&트릭
강좌/문서
자료실
볼랜드포럼 홈
헤드라인 뉴스
IT 뉴스
공지사항
자유게시판
해피 브레이크
공동 프로젝트
구인/구직
회원 장터
건의사항
운영진 게시판
회원 메뉴
북마크
IBPhoenix
FireBird Main site
볼랜드포럼 광고 모집

FireBird Q&A
[4659] 쿼리문 좀 봐주세요
송동진 [song9303] 1923 읽음    2013-11-09 15:44
Delphi XE2,Firebird 2.0 입니다.

TABLE 이 다섯 개 있읍니다.
     TABLE 1
  STYLE_NO   INPUT_DATE
    ABC             20120626
    DEF             20120627
    GHI              20120628

     TABLE 2
  STYLE_NO     LOT_NO  LOT_ITEM_NO  LOT_COLOR_NO    SIL_WT     JUNGSANG
    ABC              AAAAA1            AAAA                 AAAAA                  10
    ABC              AAAAA2            AAAA                 AAAAA                  20                 *
    ABC              AAAAA3            AAAA                 AAAAA                  30
    ABC              AAAAB1           AAAA                 AAAAB                   30
    DEF              BBBBB1           BBBB                BBBBB                 10
    GHI              CCCCC1           CCCC                CCCCC                  10

     TABLE 3
  STYLE_NO LOT_NO     ITEM_NO   COLOR_NO  G_WT
    ABC         AAAAA1        AAAA        AAAAA           15
    ABC         AAAAA2        AAAA        AAAAA           20
    ABC         AAAAB1        AAAA        AAAAB          30
    DEF         BBBBB1       BBBB       BBBBB         10
    GHI         CCCCC1       CCCC       CCCCC         10

     TABLE 4
  STYLE_NO    LOT_NO    ITEM_NO    COLOR_NO  C_WT
    ABC            AAAAA1      AAAA            AAAAA          5
    ABC            AAAAA1      AAAA            AAAAA          6
    ABC           AAAAA2       AAAA            AAAAA         20
    ABC           AAAAA2       AAAA           AAAAA          10
    ABC           AAAAA3       AAAA           AAAAA           5
    ABC           AAAAA3       AAAA           AAAAA           5
    ABC          AAAAB1       AAAA           AAAAB          10
    ABC          AAAAB1       AAAA           AAAAB          30
    DEF          BBBBB1       BBBB          BBBBB         10
    GHI          CCCCC1       CCCC          CCCCC          10

     TABLE 5
  STYLE_NO  RECIPE_NO   COLOR_NO
    ABC              111              AAAAA
    ABC              112              AAAAA
    ABC              113              AAAAA
    ABC              114              AAAAA
    ABC               115             AAAAA
    ABC               116             AAAAA
    ABC               121             AAAAB
    ABC               122             AAAAB
    DEF                222            BBBBB
    GHI                333            CCCCC

    좀 복잡 한데요 기간별로(INPUT_DATE) 조회해서
    ITEM_NO,COLOR_NO 로 합계를 구하는 건데요 TABLE 5 에 있는 RECIPE_NO 는
    COLOR_NO 별 MIN(RECIPE_NO) 이 나와야 합니다.
    또한 TABLE 2에 있는 JUNGSANG 이 * 가 있으면 각각 에 TABLE 에서 제외해야 됩니다.
    결과는 이렇게 나와야 합니다.
    STYLE_NO   INPUT_DATE     ITEM_NO   COLOR_NO  T_WT  G_WT   C_WT   RECIPE_NO
      ABC             20120626           AAAA         AAAAA         40        15         21          111
      ABC             20120626           AAAA         AAAAB         30        30         40          121
      DEF              20120627          BBBB         BBBBB       10         10         10          222
      GHI              20120628           CCCC        CCCCC       10          10         10         333

    참고로 제가 한 쿼리문 입니다.
    이렇게 하니 두배로 나옴니다.
    SELECT
        b.Style_No
        ,b.Input_Date
        ,a.Item_No
        ,a.Color_No
        ,Min(a.Recipe_No) Recipe_No
        , SUM(a.Sil_Wt)    Sil_Wt
        , SUM(a.G_Wt)      Gumsa_Wt
        , SUM(a.C_Wt)      C_Wt
    FROM (
        SELECT Style_No,0 Input_Date,Lot_Item_No Item_No,Lot_Color_No Color_No,0 Recipe_No,SUM(Sil_Wt) Sil_Wt,0 T_Wt,0 C_Wt FROM Lot_t
        GROUP BY Style_No,Lot_Item_No,Lot_Color_No
        UNION
          SELECT Style_No,0 Input_Date,Item_No,Color_No,0 Recipe_No,0 Sil_Wt, SUM(T_Wt) Gumsa_Wt,0 T_Wt FROM Gumsa_t
          GROUP BY Style_No,Item_No,Color_No
        UNION
          SELECT Style_No,0 Input_Date,Item_No,Color_No,0 Recipe_No,0 Sil_Wt,0 T_Wt,SUM(T_Wt) C_Wt FROM Chulgo_a
          GROUP BY Style_No,Item_No,Color_No
        UNION
          SELECT Style_No,0 Input_Date,0 Item_No,Color_No,Min(Recipe_No) as Recipe_No,0 Sil_Wt,0 T_Wt,0 T_Wt FROM Ord_Recipe
          WHERE Color_No = Color_No
          GROUP BY Style_No,Color_No
    ) a
    LEFT JOIN order_t b  ON b.Style_No = a.Style_No
    WHERE b.Input_Date like '201206%'
    GROUP BY b.Style_No,b.Input_Date,a.Item_No,a.Color_No

+ -

관련 글 리스트
4659 쿼리문 좀 봐주세요 송동진 1923 2013/11/09
Google
Copyright © 1999-2015, borlandforum.com. All right reserved.