说明:求在某一出厂时间段内(时间由前途输入),某类型产品的发货重量和数量(要求带APN对应的APN_DESC)小弟我写的源码如下:
SELECT TSOSOA2.APN,
sum(TSMDW01.STACKING_WT),
count(TSMDW01.STACKING_NUM)
FROM TSOSOA2,TSOSOA4,TSMDW01
WHERE
TSOSOA2.CONTRACT_NUM=TSOSOA4.CONTRACT_NUM
AND TSOSOA2.ORDER_SEQ_NUM=TSOSOA4.ORDER_SEQ_NUM
AND TSOSOA4.ORDER_NO = TSMDW01.ORDER_NO
AND SUBSTR(TSMDW01.DELIVY_TIME,1,8) BETWEEN '20070701' AND '20070731'
group by TSOSOA2.APN
如过钩上APN_DESC 发现发货重量不符合,有重复。如何解决,还求达人赐教...小弟我在线等哈
SELECT TSOSOA2.APN,
sum(TSMDW01.STACKING_WT),
count(TSMDW01.STACKING_NUM)
FROM TSOSOA2,TSOSOA4,TSMDW01
WHERE
TSOSOA2.CONTRACT_NUM=TSOSOA4.CONTRACT_NUM
AND TSOSOA2.ORDER_SEQ_NUM=TSOSOA4.ORDER_SEQ_NUM
AND TSOSOA4.ORDER_NO = TSMDW01.ORDER_NO
AND SUBSTR(TSMDW01.DELIVY_TIME,1,8) BETWEEN '20070701' AND '20070731'
group by TSOSOA2.APN
如过钩上APN_DESC 发现发货重量不符合,有重复。如何解决,还求达人赐教...小弟我在线等哈
表TSOSOA4: 字段- 订单序号(ORDER_SEQ_NUM), 合同号(ORDER_NO)
表TSMDW01: 字段- 合同号(ORDER_NO), 发货重量(STACKING_WT), 发货数(STACKING_NUM),出厂时间(DELIVY_TIME)
表TQMTP03: 字段- 产品最终用途代码(APN), 产品最终用途说明(APN_DESC)要求输出格式如下:
APN APN_DESC STACKING_WT STACKING_NUM J001 锅炉容器板 84.543 67
... ... ... ...说明:求在某一出厂时间段内(时间由前途输入),某类型产品的发货重量和数量(要求带APN对应的APN_DESC)小弟我写的源码如下:
SELECT TSOSOA2.APN,
sum(TSMDW01.STACKING_WT),
count(TSMDW01.STACKING_NUM)
FROM TSOSOA2,TSOSOA4,TSMDW01
WHERE
TSOSOA2.CONTRACT_NUM=TSOSOA4.CONTRACT_NUM
AND TSOSOA2.ORDER_SEQ_NUM=TSOSOA4.ORDER_SEQ_NUM
AND TSOSOA4.ORDER_NO = TSMDW01.ORDER_NO
AND SUBSTR(TSMDW01.DELIVY_TIME,1,8) BETWEEN '20070701' AND '20070731'
group by TSOSOA2.APN
如过钩上APN_DESC 发现发货重量不符合,有重复。如何解决,还求达人赐教...小弟我在线等哈
按你说的我钩了TQMTP03的DESC,也对DESC也做GROUP,但还是出现发货重量重复的情况。
SELECT distinct TSOSOA2.APN,
TQMTP03.APN_DESC,
sum(TSMDW01.STACKING_WT),
count(TSMDW01.STACKING_NUM)
FROM TSOSOA2,TSOSOA4,TSMDW01
WHERE TQMTP03.APN=TSOSOA2.APN
AND TSOSOA2.CONTRACT_NUM=TSOSOA4.CONTRACT_NUM
AND TSOSOA2.ORDER_SEQ_NUM=TSOSOA4.ORDER_SEQ_NUM
AND TSOSOA4.ORDER_NO = TSMDW01.ORDER_NO
AND SUBSTR(TSMDW01.DELIVY_TIME,1,8) BETWEEN '20070701 ' AND '20070731 '
group by TSOSOA2.APN ,TQMTP03.APN_DESC
发现数据-发货重量和数量大量重复、不符
这个表有重复记录?
那需要先踢重后再关联,group by ,distinct都可以还有 count(TSMDW01.STACKING_NUM) 是不是要用sum()呢?