我的sql語句如下:
SELECT distinct
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') "日期",BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "料號",
MMT.SUBINVENTORY_CODE "倉庫",
MMT.TRANSACTION_UOM "單位",
0-MMT.TRANSACTION_QUANTITY "數量"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID 日期 itemid 料號 倉庫 單位 數量
20101221 178 J010017401 WCS100 KG 256
20101221 178 J010017401 WDS100 KG 542
20101221 3264 Q02BLACK01 WES200 PCS 5
20101221 3264 Q02BLACK01 WES200 PCS 10
20101221 3264 Q02BLACK01 WES200 PCS 15
20101221 3264 Q02BLACK01 WES200 PCS 20
20101221 3378 Q030000002 WES200 PCS 4
20101221 3378 Q030000002 WES200 PCS 36
20101221 3934 U010KOYO01 WES200 PCS 3
20101221 3934 U010KOYO01 WES200 PCS 10
想進行匯總,將itemid(或料號)相同的 數量匯總,變成以下格式,
日期 itemid 料號 倉庫 單位 數量
20101221 178 J010017401 WCS100 KG 79820101221 3264 Q02BLACK01 WES200 PCS 5020101221 3378 Q030000002 WES200 PCS 4020101221 3934 U010KOYO01 WES200 PCS 13sql語句應該怎麼寫呢,哪位兄弟幫幫忙
SELECT distinct
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') "日期",BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "料號",
MMT.SUBINVENTORY_CODE "倉庫",
MMT.TRANSACTION_UOM "單位",
0-MMT.TRANSACTION_QUANTITY "數量"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID 日期 itemid 料號 倉庫 單位 數量
20101221 178 J010017401 WCS100 KG 256
20101221 178 J010017401 WDS100 KG 542
20101221 3264 Q02BLACK01 WES200 PCS 5
20101221 3264 Q02BLACK01 WES200 PCS 10
20101221 3264 Q02BLACK01 WES200 PCS 15
20101221 3264 Q02BLACK01 WES200 PCS 20
20101221 3378 Q030000002 WES200 PCS 4
20101221 3378 Q030000002 WES200 PCS 36
20101221 3934 U010KOYO01 WES200 PCS 3
20101221 3934 U010KOYO01 WES200 PCS 10
想進行匯總,將itemid(或料號)相同的 數量匯總,變成以下格式,
日期 itemid 料號 倉庫 單位 數量
20101221 178 J010017401 WCS100 KG 79820101221 3264 Q02BLACK01 WES200 PCS 5020101221 3378 Q030000002 WES200 PCS 4020101221 3934 U010KOYO01 WES200 PCS 13sql語句應該怎麼寫呢,哪位兄弟幫幫忙
解决方案 »
- 一个月份统计,求解决办法。高手来。
- oci怎么在记录中就更新数据?
- 在 pl sql 里错删除了table表,请问怎么能恢复?急,在线等带
- datastage怎么通过odbc实时连接数据库?
- 初学者碰到的问题
- 急-------------在线等----开贴给分50-----关于一条sql问题
- 向orcal插入数据,提示ORA-00911,怎么回事啊????????????急!!!!!!!!!!!!!!!!!!!!
- 面对oracle这种问题,我该怎么办?
- 求助表更新sql,请大家指点
- 大家讨论一下:EJB vs. DB store procedure
- oracle自增长列
- oracle中查表时字符串是不是加单引号或者双引号都行啊,二者有啥区别吗
SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') "日期",BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "料號",
min(MMT.SUBINVENTORY_CODE) "倉庫", --修改这里
MMT.TRANSACTION_UOM "單位",
sum(0-MMT.TRANSACTION_QUANTITY) "數量" --修改这里
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID group by to_char(MMT.TRANSACTION_DATE,'YYYYMMDD'),BIC.COMPONENT_ITEM_ID ,
MSIB.SEGMENT1,
MMT.TRANSACTION_UOM
group by 日期,itemid,料號,倉庫,單位