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
|