oracle sql語句如下:
select
to_char(MMT.TRANSACTION_DATE,'yyyymmdd') "DATEA",
msis2.serial,
msis.model,
MMT.SUBINVENTORY_CODE "SUBINV",
MMT.TRANSACTION_TYPE_Id TYPE_ID,
sum(MMT.TRANSACTION_QUANTITY ) "QTY"
from MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_FVL MSIF
,(SELECT MSIF.INVENTORY_ITEM_ID,MICV.CATEGORY_CONCAT_SEGS model
FROM MTL_ITEM_CATEGORIES_V MICV,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MICV.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID in (83) AND MSIF.ORGANIZATION_ID in (83)
AND MICV.CATEGORY_SET_ID = '1100000001') msis
,(SELECT MSIF.INVENTORY_ITEM_ID,MICV.CATEGORY_CONCAT_SEGS serial
FROM MTL_ITEM_CATEGORIES_V MICV,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MICV.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID in (83) AND MSIF.ORGANIZATION_ID in (83)
AND MICV.CATEGORY_SET_ID = '1100000002') msis2
where
TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD') between '&S_DATE' and '&T_DATE'
AND MMT.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MSIF.ORGANIZATION_ID in(83)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and MSIF.SEGMENT1 like 'A%'
and msis.INVENTORY_ITEM_ID(+) = MMT.INVENTORY_ITEM_ID
and msis2.INVENTORY_ITEM_ID(+) = MMT.INVENTORY_ITEM_ID
and MMT.TRANSACTION_TYPE_Id in('35','43')
and MMT.SUBINVENTORY_CODE in('WHF200','WMF200')
and substr(msis.model,1,1) in(9)
group by
to_char(MMT.TRANSACTION_DATE,'yyyymmdd')
,msis2.serial
,msis.model
,MMT.SUBINVENTORY_CODE
,MMT.TRANSACTION_TYPE_Id
查詢如果如下:
DATEA SERIAL MODEL SUBINV TYPE_ID QTY
數據.........
現在想改造這個sql語句,
使查詢結果的表頭QTY
當MMT.TRANSACTION_TYPE_Id=35時,QTY換成pre_com
查詢結果
DATEA SERIAL MODEL SUBINV TYPE_ID pre_com
數據.........
當MMT.TRANSACTION_TYPE_Id=43時,QTY換成back_pre
查詢結果
DATEA SERIAL MODEL SUBINV TYPE_ID back_pre
數據......... 請教一下各位兄弟,SQL語句應該如何寫?????????????
select
to_char(MMT.TRANSACTION_DATE,'yyyymmdd') "DATEA",
msis2.serial,
msis.model,
MMT.SUBINVENTORY_CODE "SUBINV",
MMT.TRANSACTION_TYPE_Id TYPE_ID,
sum(MMT.TRANSACTION_QUANTITY ) "QTY"
from MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_FVL MSIF
,(SELECT MSIF.INVENTORY_ITEM_ID,MICV.CATEGORY_CONCAT_SEGS model
FROM MTL_ITEM_CATEGORIES_V MICV,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MICV.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID in (83) AND MSIF.ORGANIZATION_ID in (83)
AND MICV.CATEGORY_SET_ID = '1100000001') msis
,(SELECT MSIF.INVENTORY_ITEM_ID,MICV.CATEGORY_CONCAT_SEGS serial
FROM MTL_ITEM_CATEGORIES_V MICV,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MICV.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID in (83) AND MSIF.ORGANIZATION_ID in (83)
AND MICV.CATEGORY_SET_ID = '1100000002') msis2
where
TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD') between '&S_DATE' and '&T_DATE'
AND MMT.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MSIF.ORGANIZATION_ID in(83)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and MSIF.SEGMENT1 like 'A%'
and msis.INVENTORY_ITEM_ID(+) = MMT.INVENTORY_ITEM_ID
and msis2.INVENTORY_ITEM_ID(+) = MMT.INVENTORY_ITEM_ID
and MMT.TRANSACTION_TYPE_Id in('35','43')
and MMT.SUBINVENTORY_CODE in('WHF200','WMF200')
and substr(msis.model,1,1) in(9)
group by
to_char(MMT.TRANSACTION_DATE,'yyyymmdd')
,msis2.serial
,msis.model
,MMT.SUBINVENTORY_CODE
,MMT.TRANSACTION_TYPE_Id
查詢如果如下:
DATEA SERIAL MODEL SUBINV TYPE_ID QTY
數據.........
現在想改造這個sql語句,
使查詢結果的表頭QTY
當MMT.TRANSACTION_TYPE_Id=35時,QTY換成pre_com
查詢結果
DATEA SERIAL MODEL SUBINV TYPE_ID pre_com
數據.........
當MMT.TRANSACTION_TYPE_Id=43時,QTY換成back_pre
查詢結果
DATEA SERIAL MODEL SUBINV TYPE_ID back_pre
數據......... 請教一下各位兄弟,SQL語句應該如何寫?????????????
分 3 次查询,你就写 3 个不同的 SQL 语句就完事了
每个 SQL 加一个 where 条件
select t.user_id,
case
when substr(t.user_id, -1) > 5 --自己写条件
then
t.user_name
else
t.SEX
end dam
from t_user t;