有两个表,t1(表头),t2(表身)t1:
SO_NUMBER SOLINE ITEM_CODE CUSTOMER ETA QTY S070172 1 C5038A001W2786GN 21390 2007-05-07 1152.0
S070172 2 C5038A001W2786GN 21390 2007-05-09 1.0t2:MRL_CODE PRODUCT SO_NUMBER SOLINE
---------- -------------------- ---------- -----------
S070172-01 C5038A001W2786GN S070172 1
S070172-01 C5038A001W2786GN S070172 2
S070172-05 P025038010PM010 S070172 1
S070172-05 P025038010PM010 S070172 2
S070172-02 A5038MANA001W2786GN S070172 1
S070172-02 A5038MANA001W2786GN S070172 2
S070172-03 A5038COBA001010 S070172 1
S070172-03 A5038COBA001010 S070172 2
S070172-04 P01503810AB10C325450 S070172 1
S070172-04 P01503810AB10C325450 S070172 2
S070172-04 P01503821AB10C325450 S070172 1
S070172-04 P01503821AB10C325450 S070172 2
S070172-04 P01503830AB10C325450 S070172 1
S070172-04 P01503830AB10C325450 S070172 2
S070172-04 P01503840AB10CCM105 S070172 1
S070172-04 P01503840AB10CCM105 S070172 2
S070172-04 P01503850AB10CCM105 S070172 1
S070172-04 P01503850AB10CCM105 S070172 2
S070172-04 P01503860AB10CCM105 S070172 1
S070172-04 P01503860AB10CCM105 S070172 2
S070172-04 P01503870AB10CCM105 S070172 1
S070172-04 P01503870AB10CCM105 S070172 2
S070172-04 P01503880AB10CCM105 S070172 1
S070172-04 P01503880AB10CCM105 S070172 2
S070172-04 P025038010PM S070172 1
S070172-04 P025038010PM S070172 2
S070172-05 P01503850AB10PCM105 S070172 1
S070172-05 P01503850AB10PCM105 S070172 2t1 和t2 的连接关系是(SO_NUMBER,SOLINE)我现在写了以下语句:
SELECT A.SO_NUMBER,A.ITEM_CODE AS ITEM_CODE1,A.CUSTOMER,A.ETA,SUM(A.QTY)AS SO_QTY,B.MRL_CODE,B.PRODUCT FROM t1 A LEFT JOIN t2 B ON A.SO_NUMBER=B.SO_NUMBER AND A.SOLINE=B.SOLINE
GROUP BY A.SO_NUMBER,A.ITEM_CODE ,A.CUSTOMER,A.ETA,B.MRL_CODE,B.PRODUCT
不能真正对t1中的QTY 汇总,由于表头t1 的ETA时间不同,用什么办法取ETA一个最小的时间,也就是以下结果:SO_NUMBER ITEM_CODE1 CUSTOMER ETA SO_QTY MRL_CODE PRODUCT
---------- -------------------- ---------- --------------------------------------- --
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-01 C5038A001W2786GN
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-02 A5038MANA001W2786GN
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-03 A5038COBA001010
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503810AB10C325450
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503821AB10C325450
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503830AB10C325450
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503840AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503850AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503860AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503870AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503880AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P025038010PM
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-05 P01503850AB10PCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-05 P025038010PM010只要SQL语句,谢谢
SO_NUMBER SOLINE ITEM_CODE CUSTOMER ETA QTY S070172 1 C5038A001W2786GN 21390 2007-05-07 1152.0
S070172 2 C5038A001W2786GN 21390 2007-05-09 1.0t2:MRL_CODE PRODUCT SO_NUMBER SOLINE
---------- -------------------- ---------- -----------
S070172-01 C5038A001W2786GN S070172 1
S070172-01 C5038A001W2786GN S070172 2
S070172-05 P025038010PM010 S070172 1
S070172-05 P025038010PM010 S070172 2
S070172-02 A5038MANA001W2786GN S070172 1
S070172-02 A5038MANA001W2786GN S070172 2
S070172-03 A5038COBA001010 S070172 1
S070172-03 A5038COBA001010 S070172 2
S070172-04 P01503810AB10C325450 S070172 1
S070172-04 P01503810AB10C325450 S070172 2
S070172-04 P01503821AB10C325450 S070172 1
S070172-04 P01503821AB10C325450 S070172 2
S070172-04 P01503830AB10C325450 S070172 1
S070172-04 P01503830AB10C325450 S070172 2
S070172-04 P01503840AB10CCM105 S070172 1
S070172-04 P01503840AB10CCM105 S070172 2
S070172-04 P01503850AB10CCM105 S070172 1
S070172-04 P01503850AB10CCM105 S070172 2
S070172-04 P01503860AB10CCM105 S070172 1
S070172-04 P01503860AB10CCM105 S070172 2
S070172-04 P01503870AB10CCM105 S070172 1
S070172-04 P01503870AB10CCM105 S070172 2
S070172-04 P01503880AB10CCM105 S070172 1
S070172-04 P01503880AB10CCM105 S070172 2
S070172-04 P025038010PM S070172 1
S070172-04 P025038010PM S070172 2
S070172-05 P01503850AB10PCM105 S070172 1
S070172-05 P01503850AB10PCM105 S070172 2t1 和t2 的连接关系是(SO_NUMBER,SOLINE)我现在写了以下语句:
SELECT A.SO_NUMBER,A.ITEM_CODE AS ITEM_CODE1,A.CUSTOMER,A.ETA,SUM(A.QTY)AS SO_QTY,B.MRL_CODE,B.PRODUCT FROM t1 A LEFT JOIN t2 B ON A.SO_NUMBER=B.SO_NUMBER AND A.SOLINE=B.SOLINE
GROUP BY A.SO_NUMBER,A.ITEM_CODE ,A.CUSTOMER,A.ETA,B.MRL_CODE,B.PRODUCT
不能真正对t1中的QTY 汇总,由于表头t1 的ETA时间不同,用什么办法取ETA一个最小的时间,也就是以下结果:SO_NUMBER ITEM_CODE1 CUSTOMER ETA SO_QTY MRL_CODE PRODUCT
---------- -------------------- ---------- --------------------------------------- --
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-01 C5038A001W2786GN
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-02 A5038MANA001W2786GN
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-03 A5038COBA001010
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503810AB10C325450
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503821AB10C325450
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503830AB10C325450
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503840AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503850AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503860AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503870AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P01503880AB10CCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-04 P025038010PM
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-05 P01503850AB10PCM105
S070172 C5038A001W2786GN 21390 2007-05-07 1153.0 S070172-05 P025038010PM010只要SQL语句,谢谢
GROUP BY A.SO_NUMBER,A.ITEM_CODE ,A.CUSTOMER,B.MRL_CODE,B.PRODUCT
你这个语句是这个结果,但是如果t1表中有多上SO_NUMBER时(销售合同),它会对应多个ETA(走货日期) ,如果按你这个
语句,所有不同的SO_NUMBER 会对应相同一个最小的ETA,这样显然不行的
..分给你们了